Convert each of the following conceptual schemas into a relational schema. Be su
ID: 3669368 • Letter: C
Question
Convert each of the following conceptual schemas into a relational schema. Be sure to list all the tables and attributes, and clearly specify primary keys and foreign key references. All answers must be typed.
Note: Use proper notation when listing tables in your relational schema. For example:
Instructor (InstructorID, Lname, Fname, Office)
Course (CourseID, Title, Credits, InstructorID)
InstructorID is a foreign key references Instructor.InstructorID
You could also use dash underline to represent a foreign key if the foreign key has the same name as the primary key it references to.
CUSTOMER LOCATION Located In Customer ID Customer Name Customer Addr (Street, City, State, ZipCode) Customer Phone Location ID Location Type Location Addr (Street, City, State, ZipCode) s chargedTime Of Day Is Charged RATE Rate Class Rate Per KWHExplanation / Answer
Applying first normal form that all key values should be atomic, in the relational schema instead of specifying Customer and Location Address as one field we have broken them to Strret,City, State and Zipcode
Customer(CustomerID, CustomerName, CustomerStreet,CustomerCity,CustomerState,CustomerZipcode,CustomerPhone)
Location(LocationID,LocationType,LocationStreet,LocationCity,LocationState,LocationZipcode)
Rate(RateClass,RatePerKWH)
Similarly based on second normal form the primary key and foreign key attributes are identified and primary key is represented using an underline and foreign key with dotted underlines. Therfore the schema becomes:
Building relationship with customer and location which is many-to many the CustomerID of Customer table can be referred in location table using foreign key relationship. To achieve this the schema can be further modified into the following
Customer(CustomerID, CustomerName, CustomerStreet,CustomerCity,CustomerState,CustomerZipcode,CustomerPhone)
Location(LocationID,CustomerIDLocationType,LocationStreet,LocationCity,LocationState,LocationZipcode)
Rate(RateClass,RatePerKWH)
Similarly LocationID can be referred in Rate table to identify the location and the rate details of that location. Hence the relation schema becomes:
Customer(CustomerID, CustomerName, CustomerStreet,CustomerCity,CustomerState,CustomerZipcode,CustomerPhone)
Location(LocationID,CustomerIDLocationType,LocationStreet,LocationCity,LocationState,LocationZipcode)
Rate(RateClass LocationID,RatePerKWH)