Homework 4 To design databases

CSCI 330 Database Systems: Homework 4
Total Point: 20 (5% of course grade)
Goal
To design databases (E-R diagram and schemas) discussed in chapter 7.
Q1 (10 points)
Suppose an automobile company (e.g., Toyota) hired you to design a database to assist
• Its dealers for maintaining customer records and dealer inventory
• Sales staff in ordering cars.
The database needs to store information about
• Brands (e.g., Toyota, Lexus)
• Models (e.g., Camry, Rav4)
• Options (e.g., Basic, premium, Prestige)
• Individual dealers (e.g., Wilson Motors)
• Customers
• Vehicles/Cars
You need to remember the following information:
• Each brand is identified by brand name (e.g., Toyota, Lexus).
• Each model has model_id and name. Each model is identified by the model_id.
• Each model must be associated with a brand. i.e., there is no model without a brand.
• Each vehicle is identified by VIN (vehicle identification number).
• Each vehicle must be associated with a model, i.e., there is no vehicle without a
model.
• Each model may have many options. For instance, the Rav4 model has three options
(e.g., LE, XLE, LE Hybrid).
• Each option can have option_id and specification. Each option is identified by
option_id.
• Each dealer has dealer_id, name, and address. Each dealer is identified by dealer_id.
• Each customer has customer_id, name, and address. Each customer is identified by
customer_id.
• Each vehicle may be associated with a dealer.
• Each vehicle may be associated with a customer.
(7 points) Draw an E-R diagram to represent the database mentioned above. You must
keep the following in mind.
• There are 6 strong entity sets and NO weak entity sets. Some entity sets might
have a single attribute.
• There are 12 attributes and all of them are simple and single-valued attributes.
• There are 5 binary relationship sets. There are no other types of relationship sets
(e.g., ternary).
• NO relationship set has any descriptive attributes.
• There are 2 total participation.
• In your ER diagram, you will also have to do the following:
o Identify the attributes for each entity set. Please underline the primary key.
o Specify the cardinality (one to one, one to many, many to one, or many-many)
of the diagram.
(3 points) Determine the Relation Schemas from the E-R diagram. No optimization is
required.
Q2 (10 points)
Suppose a worldwide packet delivery company (e.g., FedEx) hired you to design their
database. The database needs to store information about
• customer
• packet
• place
You need to remember the following information:
• Each customer has a unique customer_id. Each customer also contains their name
and address.
• Each packet has packet_id and weight. Each packet is identified by its packet_id.
• Each place or location has place_id, city, country, and address. Each place can be
identified by place_id.
• Each packet must be associated with a place, i.e., there is no packet without a place.
• Each packet must be associated with a customer, i.e., there is no packet without a
customer. The association between customers and packets can be in one of the
following ways:
o Customer who sends packets. In this case, the customer provides the
time_sent information, i.e., when the packet was sent.
o Customer who receives packets. In this case, the customer provides the
time_recieved information, i.e., when the packet was received).
o Some customers may play the role of both sender and receiver.
(7 points) Draw an E-R diagram to represent the database mentioned above. You must
keep the following in mind:
• There are 3 strong entity sets and NO weak entity sets.
• There are 9 attributes for the entity sets and all of them are simple and singlevalued attributes.
• There are 3 binary relationship sets. There are no other types of relationship sets
(e.g., ternary).
• Two relationship has two descriptive attributes
• Two entity sets may be related by two separate relationship sets.
• There are 3 total participation.
• In your ER diagram, you will also have to do the following:
o Identify the attributes for each entity set. Please underline the primary key.
o Specify the cardinality (one to one, one to many, many to one, or many-many)
of the diagram.
(3 points) Determine the Relation Schemas from the E-R diagram. No optimization is
required.
What to submit
All E-R diagrams and Relation schemas.
Submission Instructions
• Put all E-R diagrams and Relation schemas in one single doc/docx file.
• Convert the file to a pdf file. The file name should be YourLastName-330-HW4.pdf.
• Upload the pdf file on canvas.
Late Policy
• No late work will be accepted