IT 8100 DATABASE ARCHITECTURE AND DESIGN

 IT 8100 DATABASE ARCHITECTURE

AND DESIGN

Group Activity

3

“Student Information

System”

ERD Conceptual Model and

Structured Query Language (SQL)

Group Activity

3 “Student Information System”

3

ERD Conceptual Model

1.

Create the Entity Relationship

Diagram ERD)



Create the Entity Relationship

Diagram ERD) for the following

tables:



Student



Course



Offering



Faculty



Enrollment

2. Convert the Entity ER

Diagram to Relational

Schemas

Convert the Entity ER

Diagram to Relational

Schemas.



Create the appropriate

Relationships, Cardinality

1 1, 1 N, N 1, M N ), and

Key Fields



Use these attributes for your tables:



Student



stdSSN



stdFirstName



stdLastName



stdCity



stdState



stdZip



stdMajor



stdClass



stdGPA



Course



CourseNo



crsDesc



CrsUnits



O ffering



OfferNo



CourseNo



OffTerm



OffYear



OffLocation



OffTime



FacSSN



OffDays



Faculty



FacSSN



FacFirstName



FacLastName



FacCity



FacState



FacZipCode



FacRank



FacHireDate



FacSalary



FacSupervisor



FacDept



Enrollment



OfferNo



StdSSN



EnrGrade

3

. Write the Structured Query

Language (SQL ) for the Relational

Schemas



Write the Structured Query

Language (SQL) for

the Relational Schemas



Declare the appropriate

column names, data types,

lengths and constraints

4. Write Structured

Query

Language (SQL ) Commands



Write and enter the SQL commands to add records into your

Student Information System tables (at least 5 records in each table).



Write the SQL commands to produce four different reports,



Including joins between tables.



Queries using l ogical, arithmetic, and comparison operators.



Sort the reports by different tables.