Database Systems

Aim:

• To develop a database for the given case study by drawing the EER diagram
• To apply the normalization rules to the data structures and hence to show the UNF, INF, 2NF, and 3NF relations of the given scenario
• To create and manipulate database.

Assignment consists of two parts:

Part 1 - ER Modeling

Part 2 - Normalization and Building database

CASE STUDY:

SPC is a private education provider (PEO) offering a wide range of short courses. The courses offered are either language training courses or IT training courses. Students enrolled into the courses are assigned to different classes. Each class is identified by a class code.  Other details of a class include the date for the start (SOC) and the end (EOC) of the class, and the daily schedule of the class. The daily schedule shows the classroom, and the time for each lesson. Each course is identified by a course number. Details of the course include the course title, description, and the course fee. SPC hires some fulltime Instructors. It also maintains a list of part time instructors. Fulltime instructors are employees of the organization and thus are identified by the employee IDs. Only one instructor, fulltime or part time, is assigned to a class. Each instructor is associated with a list of courses that the instructor is capable of teaching. Part time instructors are assigned to the classes on contracts.

As the business is expanding, the number of employees such as Sales, Operation, and Instructor grow. As a consequence, there are many meetings within SPC. Any employee may call for a meeting by inviting other employees for the discussion on a certain issue. Other details of the meeting include the title, the agenda, the chairperson, the status and the minute of the meeting once the meeting is over. The minute taker is selected from one of the invitees of the meeting.

Answer all the following questions. State clearly any assumptions you made.

Part 1 - ER Modeling

Question 1:

a) Candidates must provide a written description of the company's operational procedures and the list of Business Rules that are derived from that description.

The assignment should at least cover all of the following but should not be limited only to these features.

b) In the design of a database, the first step is drawing the conceptual model (ER diagram).

With reference to the case study given above, perform the following tasks:

i) Identify all the real world entities giving a candidate key and suitable attributes for each entity.

ii) Identify an entity with a composite attribute.

iii) Identify an entity with a multi-valued attribute.                               

iv) Identify a weak entity present in the case study and determine its relationship with the owner entity.

v) Identify any one relationship with a cardinality ratio 1:1.

vi) Identify any one relationship with cardinality ratio 1: M.                 

vii) Identify any one relationship with cardinality ratio N: M.

viii) Draw a complete ER diagram.

Part 2 - Normalization and Building database

General Information:

This part is designed for a team work of maximum 2 students. It is your responsibility to form your team. However, if you prefer working individually, that is fine but no extra mark is to be added for the heavier workload.

Question 1:

Apply the normalization rules to the following data structures, which show the data to be recorded in a database of student records. All of your intermediate steps (UNF, INF, 2NF, and 3NF) should be shown in detail.State any assumptions that you make.

Question 2:

i) Translate the E-R diagram into relational database tables and specify their relationships appropriately.

ii) The physical database can be created using DDL (Data Definition Language) and manipulated using DML (Data Manipulation Language), which enable users to access or manipulate data as organized by the appropriate model.