ODA BULTUM UNIVERSITY
COLLEGE OF NATURAL AND COMPUTATIONAL SCIENCE
DEPARTMENT OF COMPUTER SCIENCE
Fundamentals of Database Systems (CoSc2041)
Practical Assignment for 2nd year students. (Form 3 students per group)
Evaluation: Design (10%) → Implementation and Demonstration (10%) = 20%
General Instructions: Understand the following project scenario and perform all
under listed tasks for each scenario.
PROJECT 1: ENROLLMENT MANAGEMENT SYSTEM
Assume that, ABC College needs to develop a database in order to store information about
enrollment and you are invited to design & develop the database based on the following
information. Student has Unique-ID, Name, DOB, Gender & Mobile. Each student takes
several courses and one course should be taken by many students. Course has unique
Course_Code, unique Course_name and Credit_hour. The database also expected to store
Instructors‘ profile and Instructor has Unique- ID, Name, Gender, Age, Date of Hire, Year of
service which is computed from Date of Hire, Qualification, and Phone. Each instructor may
teach more than one courses and one course should be delivered (thought) by different
instructors. The database will be designed to store mark, Grade & registration date of each
student of those who attend & complete the courses. In addition the database also keeps the total
number of hours thought by each instructor.
Based on the given information (requirement) perform the following tasks:
a) identify Entity, attributes and relationship among the entity and relationship cardinality
b) Draw ER-Diagram using standardized symbolic notation
c) Map ER-Diagram to relational database schema
d) Normalize database up to 3NF
e) Create database and all identified and normalized tables under created database using SQL
commands based on created relational database schema
f) Set all database constraints where it is appropriate based on the organizational business rules
g) Insert at least 5 records including your group members and display the result
h) Change the database structure by adding 2 fields & removing one field from 2 added fields on all
tables k) Modify at least two records from each table
l) Use aggregate function where it is necessary
m) Retrieve and display the result from all tables as one table
n) Create sample report from organizational database
PROJECT 2: ICT ACCESSORIES MANUFACTURING & SALES SYSTEM
ODAA PLC is modern ICT Accessories manufacturing company which produces and supplies
different types of ICT related products in Ethiopia. The products are described by: product ID,
product name, quantity, and price. The products are sold to different customers. The company
also keeps track of its customers’ information as customer ID, customer name, customer address.
The customer makes orders to buy products from the company at different times. It is also
necessary to keep order information, which includes order ID, product ID, customer ID, order
quantity, and order date. Based on this organizational information perform the following tasks:
a) Identify and list the entities, attributes, and relationships for the ODAA PLC manufacturing
company database
b) Construct an ERD for the data model of ODAA PLC manufacturing company.
c) Map ERD to relational database schema for the organization
d) Normalize database up to 3NF
e) Create database and all tables under created database using SQL commands
f) Set all database constraints where it is appropriate based on the organizational business rules
g) Insert at least 5 records for each table of database
h) Use aggregate function where it is necessary
i) Retrieve and display the result from all tables as one table
j) Create sample report from organizational database
PROJECT 3: UNIVERSITY LIBRARY MANAGEMENT SYSTEM
Suppose you are hired as database administrator in one of educational institution and the
organization is requested you to develop a library management system for the institution. In this
system, a student is identified by a Student number and name. A student can borrow a maximum
of three books. Each book is identified by an ISBN and BookName. Many students can borrow
many books. Every borrow transaction is recorded using StudentNo, Date Borrowed and Return
date. Based on this information perform the following tasks:
a) Identify the entities, attributes and relationship in this system and the respective attributes.
b) Construct an ERD for the data model
c) Map ERD to relational database schema for the organization
d) Normalize database up to 3NF
e) Create database and all tables under created database using SQL commands
f) Set all database constraints where it is appropriate based on the organizational business rules
g) Insert at least 5 records for each table of database
h) Use aggregate function where it is necessary
i) Retrieve and display the result from all tables as one table
j) Create sample report from organizational database
INDIVIDUAL ASSIGNMENT: 15%
1. Normalize the following un-normalized table into 1NF, 2NF and 3NF with brief and detail
Explanations
Student Student Major Course Course Instruc Instructor Instructor Grade
ID Name Code Name torID Name office
OBU- Chala Information IS460 Computer 401 Jabessa A104 A
3546 System Network
OBU- Biftu Computer CS530 Database 503 Murti A106 C
5845 Science system
OBU- Dani Information IS460 Computer 401 Jabessa A104 B+
7845 System Network
OBU- Helena Information IS460 Computer 401 Jabessa A104 C+
7689 System Network
2. Perform the following for a given table SALES (OrderID, CustomerName, CustomerPhone, ProductID,
ProductName, Quantity, Price, Total) and Functional Dependencies:
OrderID → CustomerName, CustomerPhone
ProductID → ProductName, Price
OrderID, ProductID → Quantity, Total
Total = Quantity × Price
Required:
a) Identify normalization problems
b) Normalize to 3NF by showing each step
c) Create SQL statements for normalized tables
3. Design complete database for online exam system with:
Students take multiple exams
Each exam has multiple questions of different types (MCQ, Essay, True/False)
Questions can be reused in different exams
System tracks time taken per question
Automatic grading for objective questions
Required:
a) Identify minimum 6 entities with attributes
b) Show relationships with cardinalities
c) Design ER diagram
d) Write SQL to create main tables
4. Suppose Oda Bultum University has two types of students: Undergraduate and Post_Graduate. Both
share common attributes (ID, Name, Email), but Undergraduates have High School info, while
Post_Graduates have Bachelor Degree info. Show this using EER specialization notation with
appropriate constraints and then perform the following tasks:
a) Transform to relational database schema.
b) Map database schema to SQL