Objective: This is an individual assignment aimed to give the student exposure to the concepts relating to database systems and optimal strategies for efficient management of databases
Learning Outcomes assessed:
1. Create E-R diagrams.
2. Normalize tables.
3. Design a database.
Task 1:
Submit a work proposal for this assignment by the end of week 8 which must include:
a) Understanding of deliverables - a detail description of deliverables.
b) General overview of proposed plan - initial understanding of solution to task 2 which includes, the name of entities, associative entities and relationships. Initial understanding of solution to task 3.
c) Timeline for completion of task 2 and task 3.
The work proposal must be submitted in a word file through the link available in Moodle. The proposal can also include draft answers.
Scenario:
Polyglot institute conducts Certification Exams. These exams are basically organized by different certification councils. The institute operates in the following way.
A candidate identified by a unique id, name, address and mobile no. should register to write an examination. A candidate can choose and register one or more certification exams. However one exam has to be registered by at least one candidate. Every exam is identified by its code, name and council name. The institute would like to track the number of attempts of each candidate when he/she registers for an exam.
When a candidate registers for an exam, the institute allocates room identified by its no, building name and location. An exam can be scheduled in at least one room and a room may be scheduled for more exams or may not be scheduled for any exams at all. In order to avoid clashes, the institute needs to store the exam date whenever a room is scheduled for an exam.
The institute assigns one invigilator to each room. Each invigilator is identified by his/her ID, name, contact no. and specialization. An invigilator is assigned to exactly one room and a room should have one invigilator compulsorily. An invigilator belongs to exactly one certification council. Each council is designated by its code, name and country. It is not mandatory that a council should have invigilators.
Task 2:
a) Construct the Entity Relationship Diagram (ERD) for the above given scenario. Identify all the entities, attributes of each entity including primary key, not null, foreign key, relationship between the entities and cardinality constraints. State any assumptions necessary to support your design.
b) Write an SQL statement to create the tables and insert at least 2 records for each table created above.
c) Write at least two SQL statements having "subqueries" based on the tables created above to demonstrate the concept of "subquery".
d) Write at least two SQL statements having "joins" based on the tables created above to demonstrate the concept of "Join".
Task 3:
a. Normalize the below given import summary form to First Normal Form, Second Normal Form and Third Normal Form.
Import Summary
Import id: IM92-2016 Manager id: M567
Import Date: 2/2/2016 Manager name: Ali Al Rawahi
Source: Dubai
Destination: Oman
Import Parts Details:
|
Partscode
|
Partsname
|
Type
|
Materialtype
|
Weight
|
Quantity
|
Total Weight
|
|
P2345
|
Couch
|
Furniture
|
Leather
|
400
|
150
|
60,000
|
|
P3413
|
Door
|
Building Accessory
|
Aluminum
|
85
|
1,500
|
127,500
|
|
P2415
|
Office Chair
|
Furniture
|
Leather
|
70
|
600
|
42,000
|
|
P4424
|
Sofa
|
Furniture
|
Wood
|
300
|
400
|
120,000
|
|
P2476
|
Door Knob
|
Building Accessory
|
Iron
|
30
|
600
|
18,000
|
|
P3412
|
Window frame
|
Building Accessory
|
Aluminum
|
100
|
400
|
40,000
|
|
P3422
|
Office table
|
Furniture
|
Wood
|
250
|
100
|
25,000
|
b) Write your refection by describing the normalization process carried in the task above in connection with concepts taken from literature in 400-500 words. The literature can include conference papers / scholarly articles / text / reference books.
Task 4:
Be ready for a presentation and Viva to demonstrate your knowledge with the different concepts used in preparing the assignment. Schedule for the presentation will be announced in the class. Marks for task 2-3 will depend on the presentation and viva.