database project

CSC4341 Principles of Database Systems Fall 2021

HW 3 – Database Design (Part I)

Objectives

• Be able to draw E/R diagrams for application problems.

• Be able to convert an E/R diagram to relational database table design.

• Be able to create tables on real database system.

Key Ideas

• E/R diagram

• Mapping between E/R diagram and relational database tables.

Homework Problems

Reference: Lecture notes and handouts

1. Work on 14.7 and 14.8 on page 430.

Instructions:

• Draw E/R diagrams for each of the two application problems. • Convert the E/R diagrams to relational database tables. • Create tables on the MySQL database on the course server based on the E/R

diagrams.

• Insert some sample data records into the tables to check if the design reflects the requirements of the application problems.

• Revise the design if necessary. • Submit all the work (a single pdf file) to Blackboard. The file includes E/R diagrams

either by hand or software drawing, relational database tables with sample data records populated, and preferably test SQL queries.

Notes:

• Although a flow chart is given for 14.7, it doesn’t mean you have to create an entity for each box. It just gives you an idea on what information should be stored in the database.

• For 14.8, to simplify the design, assume that an order consists of a number of order lines (or called line items) with item id, quantity of the item, subtotal, etc. Each order line is associated with an item. “Item” is just a generic product such as SanDisk 64G memory card. We don’t distinguishe each individual “instance” of SanDisk 64G memory card.

• If the descriptions of the application problems are not clear to you or not given at all, state the assumptions you use in your design.

• The standard notations for E/R diagram and relational table mapping rules should be followed.

Page 2