Lab-manual
INF 370
Database Management Systems
Lab-Manual
Academic Year 2020 – 2021
Prepared by : Nagwa Yaseen Hegazy
Student ID
Student Name
Group
INF 370 Database Management Systems
Lab Manual
2
INF 370 Database Management Systems
Lab Manual
Lab-4
1) Deign an enterprise data model for ABC Company. ABC Company is a production
company that works on many projects. The policy of the ABC company is as follows:
Each employee works in only one department.
Each employee may be assigned to none or more than one project.
Each Project has a project manager who manages a team of employees.
The team member has at least one employee from each department in the company.
Each project has many tasks assigned to team members.
Each task has a description, resources, start date, due date, an outcome.
Project manager assigns tasks to employees and tracks the project progress.
The project manager reports project status to top management on biweekly basis.
Q1. Define all entities.
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
Q2. Define completely the meta-data for each data item using the next table notation.
Entity:
Data item Meta Data
Name Type Length Min Max Description Source
3
INF 370 Database Management Systems
Lab Manual
4
INF 370 Database Management Systems
Lab Manual
Q3. Identify the business rules from the description paragraph? How it will affect the
database design?
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
…………………..……………………………………………………………………………………
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
Q4. Draw the conceptual model diagram for ABC Company.
5
INF 370 Database Management Systems
Lab Manual
2) CIC has a registration unit for students in Computer Science Institute. We need to design a
database system for CS Students Registration Unit. The registration process follows CS
Bylaw rules and regulation which are:
Courses are divided into three categories
Mandatory Elective Total no. of Total no. of
CS Program
Courses Courses Courses Credit Hours
University Requirements 10 2 12 38
Institute Requirements 13 2 15 52
Major Requirements 12 4 16 50
Total 35 8 43 140
Students register according to GPA. The least credit hours is 12 and maximum of 6
courses (18 credit hours).
Range GPA Max. Credit Hours
2 < GPA 22
2 = < GPA < 2.75 21
27.1 = > GPA 21
A course may or may not have a prerequisite.
A student registers to a course when he/she passed the prerequisite courses.
A student may register for an already passed course to increase his/her GPA.
Each course has a number of contact hours for Lectures, Tutorial and Lab.
CIC
Pre- Cred. La
Course Course Name Lec. Tut. Course Type
requisites Hour b.
Code
University
BSC 125 Mathematics - 2 BSC 122 3 2 3 0
Requirements
University
BSC 124 Electronics BSC 121 4 3 0 3
Requirements
Fundamentals of Structured Institute
CSC 150 CSC 140 4 3 0 3
Programming Requirements
Database Management Institute
INF 370 CSC 140 4 3 0 3
Systems Requirements
University
BSC 225 Discrete Mathematics BSC 122 3 2 3 0
Requirements
Q1. Define all entities.
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
6
INF 370 Database Management Systems
Lab Manual
Q2. Define completely the meta-data for each data item using next table notation.
Entity:
Data item Meta Data
Name Type Length Min Max Description Source
7
INF 370 Database Management Systems
Lab Manual
Q3. Identify the business rules from the description paragraph? How it will affect the
database design?
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
Q4. Draw the conceptual model diagram.
8
INF 370 Database Management Systems
Lab Manual
ER-Notations
Examine the next figure for Entity, Entity type, relationship degree, relationship cardinality.
Use those notations to draw Entity Relationship Model for ABC Company and CS Students
Registration Unit
Notes:
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
……………..…………………………………………………………………………………………
…………………..……………………………………………………………………………………
……………..…………………………………………………………………………………………
9
INF 370 Database Management Systems
Lab Manual
3) Revision questions:
a) What is the difference between strong and weak entities?
……………..………………………………………………………………………………………
…………………..…………………………………………………………………………………
……………..………………………………………………………………………………………
…………………..…………………………………………………………………………………
……………..………………………………………………………………………………………
…………………..…………………………………………………………………………………
b) What is the difference between an entity and its instance?
……………..………………………………………………………………………………………
…………………..…………………………………………………………………………………
……………..………………………………………………………………………………………
…………………..…………………………………………………………………………………
……………..………………………………………………………………………………………
…………………..…………………………………………………………………………………
c) Give examples for three business rules?
……………..………………………………………………………………………………………
…………………..…………………………………………………………………………………
……………..………………………………………………………………………………………
…………………..…………………………………………………………………………………
……………..………………………………………………………………………………………
…………………..…………………………………………………………………………………
d) Give examples for each attribute classifications:
i. Required versus optional attributes
……………..………………………………………………………………………………………
…………………..…………………………………………………………………………………
……………..………………………………………………………………………………………
ii. Simple versus composite attribute
……………..………………………………………………………………………………………
…………………..…………………………………………………………………………………
……………..………………………………………………………………………………………
01
INF 370 Database Management Systems
Lab Manual
iii. Single-valued versus multivalued attribute
……………..………………………………………………………………………………………
…………………..…………………………………………………………………………………
……………..………………………………………………………………………………………
iv. Stored versus derived attributes
……………..………………………………………………………………………………………
…………………..…………………………………………………………………………………
……………..………………………………………………………………………………………
v. Candidate attribute
……………..………………………………………………………………………………………
…………………..…………………………………………………………………………………
……………..………………………………………………………………………………………
vi. Identifier attributes
……………..………………………………………………………………………………………
…………………..…………………………………………………………………………………
……………..………………………………………………………………………………………
e) What is an associative entity? Give an example?
……………..………………………………………………………………………………………
…………………..…………………………………………………………………………………
……………..………………………………………………………………………………………
…………………..…………………………………………………………………………………
……………..………………………………………………………………………………………
…………………..…………………………………………………………………………………
00
INF 370 Database Management Systems
Lab Manual
4) Draw the Entity Relationship Model for ABC Company?
02
INF 370 Database Management Systems
Lab Manual
5) Draw the Entity Relationship Model for CS Students Registration Unit?
03
INF 370 Database Management Systems
Lab Manual
6) Additional Exercises
Select one of the following and redo Q1 to Q4:
We are going to design a database for XY Hospital. The main purpose is to enable and
to enhance different services such as scheduling patients’ appointments with doctors in
different types of clinics. Also, we need to store all employees’ data.
We are going to design a database for CS Library. A library has different types of
resources in different subjects. All CIC students are the users of CS library.
We are going to design a database HNR Bank. HNR Bank has many branches which
provide many services to its customers.
Solution:
04
INF 370 Database Management Systems
Lab Manual
05
