IST210 1

Database Design I(In-Class Exercise Answer)

IST 210: Organization of Data

IST210 2

In-class Exercise 1Transform this diagram into tables

UserID

NameEmail

SpotID

LocationMonthlyCost

USERPARKING

PARKING(SpotID, Location, MonthlyCost)USER(UserID, Name, Email, SpotID)

PARKING(SpotID, Location, MonthlyCost, UserID)USER(UserID, Name, Email)

Note: both options are correct, but the first one is preferred because of the minimal cardinality of SpotID is mandatory.

IST210 3

In-class Exercise 2Transform this diagram into tables

BUILDING(BuildingName, Address)APARTMENT(BuildingName, ApartmentID, NumberOfBedrooms, NumberofBaths, Rent)Notes:• A common mistake is making ApartmentID as the single primary key. (BuildingName,

ApartmentID) should be a composite primary key for APARTMENT • Another common mistake is to create a duplicate attribute BuildingName in

APARTMENT as the foreign key, or forget to make BuildingName as the foreign key

IST210 4

In-class Exercise 3

CourseID

CourseNameInstructor

StudentID

StudentNameEmail

COURSESTUDENT

Transform this diagram into tables

STUDENT(StudentID, StudentName, Email)COURSE(CourseID, CourseName, Instructor)REGISTRATION(StudentID, CourseID)

Notes: In REGISTRATION table, StudetID and CourseID are both primary key and foreign key.