Assignment Description
Part A: (20 marks)
Paste below the summary of your Assignment 2 online quiz on Moodle. The quiz will be open in week 9 laboratory class.
Part B: (70 marks)
- Database Design
Assume that you are the database developer for this task, complete the information-level design for the new database for “MIT_short_courses” that satisfies the constraints and user view requirements given below. Create a Crow’s Foot Notation Entity Relationship Diagram (ERD) to support the following business operations. MIT is planning to design a data model to hold information relating to their Courses, Students, Coordinators, Instructors and Classes. User requirement / business rule 1:
As a database designer for “MIT_short_courses”, you need to ensure that each student can register for one or more short courses.
You need to store students’ details including students ID, first name, last name, gender, age, and postal code.
User requirement / business rule 2:
Each course has one Coordinator assigned to look after general administration of the course. However, each Coordinator may be assigned to one or more courses.
System stores Coordinator’s code, name and the school he/she attached to.
Each course offers zero or more classes. Some courses are research based they do not offer classes.
Each Course has an ID (for unique identification), title, credits and prerequisites.
User requirement / business rule 3:
Each class has an ID, Section no, Date and Time.
Each class is taught by an instructor.
Instructors can teach in one or more scheduled classes. However, some Instructors are working with research students therefore no classes allocated to them in the system.
Write down any assumptions you have made, if they are not explicitly described here.
While completing the information-level design you are required to answer questions given below.
a. Identify and list entities described in the given case scenario. (5 Marks)
b. ER diagram
Add attributes to those entities and select suitable primary keys for each entity.
Represent the structure of your database visually by using the entity-relationship (ER) diagram.
I. You are required to mark all cardinality on the relationships. if you make any assumptions (When you mark cardinality) about data that are not explicitly given in the problem, these must be described. (5 Marks)
II. You are required to use a software tool to create the ER diagram. You can use draw.io[2], Lucidchart [1], Visio or any other software tool to create the ER diagram.
Note: All many to many relationships must be replaced with bridge tables. (20 Marks) - Data Integrity
In this question you will transform an entity-relationship (ER) Diagram into a relational schema (data model) yoy need to ensure that the resulting relations are in 3NF.
a. First, represent entities and attributes as collection of tables and attributes.
Eg. Student (Stu_ID, Sname,…….
NB: Select suitable primary key for each table and underline them. (5 Marks)
b. Determine the functional dependences. (5 Marks)
Eg. Stu_ID — > Sname, , ……, ……
c. Normalise these tables. Make the normalization to 3NF and explain every step in the process. (10 Marks) - Model Building
Build this model using MS Access/SQLite by creating these tables and Relationships.
Populate these tables with appropriate data, at least 2 records in each table.
Include following into your MS Word document. (10 Marks)
I. If you are using MS Access include following screen shots
Relationship diagram created in Access. (Select database tools Relationships in the Access menu)
Data sheet view and design view of your tables as given below in Figure 1 and 2. For Student_Name and Student_ID you need to use your MIT ID and your name as one data record.
Figure 1 Data Sheet View Figure 2 Design View
II. If you are using SQLite
Include screen shots of CREATE TABLE command and INSERT INTO commands. - Report Generation
a. Write a query to extract useful information from this “MIT_short_courses”, database.(3 Marks)
b. Execute this query on the database and attach the screenshot of your output. (2 Marks) - Research Question
MongoDB is a popular general purpose database platform. Write a brief description about this database by comparing the features in MS Access.
Answer with at least 150 words using suitable citations and references for the materials you used for this work. Use IEEE reference style.
