RDB Design Lab 2

Using the entities and functional dependencies identified in Group Lab 1 where you identified functional dependencies for students and classes, prepare a report which identifies tables and columns, and identifies the primary keys and foreign keys for these tables in your database.

Your database design should be third normal form (3NF), meaning you should make an effort to identify tables (entities) with columns (attributes) which are dependent on a primary key in that table. The exercises we did in class concerning what belongs should be used to develop this design correctly and any columns (attributes) which do not belong in the table (are not dependent or are not identified by the primary key) should be modeled in a separate table. All tables should have a primary key and should have only one primary key as required by 3NF designs in a relational database.

For each table in your database, prepare a document table as shown in the example below.

Table Name: Student

Column Name	Primary Key	Foreign Key
column1	Yes	No
column2	No	No

Use the below entity and attribute to create the table:

a. Schema: Student(IDNumber, Name) FD: IDNumber = Name Schema:Dorm(DormId, DormName, DormAddress) FD: DormId = DormName, DormAddress Schema: Club(ClubId, ClubCost) FD: ClubId = ClubCost Schema: StudentPhone(IDNumber, PhoneNumber) FD: IDNumber = PhoneNumber Schema: Room(RoomNumer, RoomType, DormCost, DormID) FD: RoomNumber, DormID = RoomType, DormCost Schema: StudentRoom(RoomNumber, IDNumber)

Schema: StudentClub(ClubID,IDNumber)

b. Use the below entity and attribute to create the table :

Schema: Class (IDNumber, Name, Description, CreditHours, Professor, PeriodID, RoomNumber, NumberofStudents)

FD: ClassID = IDNumber Schema: Period (Semester, PeriodStart, PeriodEnd) FD: PeriodID = Semester, PeriodStart, PeriodEnd Schema: Test (ClassID, TestNumber, TestAverage) FD: TestID = ClassID, TestNumber Schema: Professor (Professor, DepartmentID, DepartmentName)

FD: ProfessorID = Professor, DepartmentID