Project: Database Project

Deadline: 15 APR

[20200409~20200422] Period of Project - 22 APR

This is a team project, up to four people, that aims to provide you some experience in database design. We will explore both theoretical and practical aspects of the database design steps.

First, you will design an application and then translate the theoretical concepts into the terms of a concrete DBMS to implement the database.

The objective in this project is

• to adapt the existing University database from our textbook and
• to turn in the database of the University of Macau.

Procedure

A. Discuss the changes your group would like to implement in the existing DB to become UM DB.

As follows some suggestions:

•  Alter attributes to accommodate data from University of Macau.
  For example, you need to alter the size of the student ID from (5) to (8) or (11) in order to accommodate DB012345 or D-B0-1234-5.
•  The group may change primary keys in case your group think it is necessary.
•  The group can add any extra constraints or remove any existing one.
•  The group may add attributes if you think necessary.

B. Specify the assumptions about the database in English (informally).

Here you give the proper explanation of the attributes, keys, the nature of relationships between entities, etc. For those obvious assumptions such as students can take several courses, be brief. However, for those more complex relationships you should explain in a more detailed way. In addition, do not make too many simplifying assumptions.

C. Give an ER diagram

which will reflect your choice of the entity sets, their relevant attributes, and the relationships among them. Again, make your relationships realistic and meaningful.

E-R Diagram for a University Enterprise

D. Convert the ER diagram into a relational database schema.

List all of your SQL statements for creating or altering tables, inserting or updating tables and so on.

E. Samples from your database.

Few tasks to be made here:

•  Update the values of the altered attributes.
  The number of tuples in the relations should not be different from the existing relations. List all relations of your database.

•  Create and list ten reasonable queries
  (containing ‘join’ or ‘aggregate’).
  Execute them and print the contents for checking.