Database Management Systems

1
Database Management Systems

• IT 2302

2
Todays Lesson

• ER diagram

3
A Mini-World Example

• A Company is organized in to departments. Each
  department has a number and an employee who
  manages the department. We keep track of the
  start date when that employee started managing
  the department. A department may have several
  locations.
• A department controls a number of projects. Each
  of which has a name, a number and a single
  location.

4

• We store each employees name, national Id
  number, address, salary, birth date and sex. An
  employee is assigned to one department, but may
  work on several projects, which are not
  necessarily controlled, by the same department.
  We keep track of the number of hours per week
  that an employee works on each project. We also
  keep track of the direct supervisor of each
  employee.

5

• We keep track of the dependants of each employee
  for insurance purposes. We keep each dependants
  name, sex, birth date and relationship to the
  employee.
• Such information is gathered from the mini-world
  to perform Phase 1 of database design process.
  i.e. Requirements Collection and Analysis Phase

6
Conceptual Design

• All the requirements collected at Phase 1 are
  analysed to create a Conceptual Schema.
• This process is called the Conceptual Design.
• We identify the entities, their attributes,
  relationships and constraints (business rules).
  The conceptual schema is used as a reference to
  ensure that all users data requirements are met
  and the requirements do not include any
  conflicts.

7
Cont

• Entities
• Dept.
• Employee
• Project
• Dependent

8

• Relationships
• A Dept has Many Employees
• An Employee works for A Dept.

9
one to many relationship
Department
Personnel
Sales
Employee
Pat
Dias
Ane
Kate
Alwis
Perera
Silva
Tom
Jane
De Silva
10
Relationships

• Relationship Type
• A meaningful association between association
  between (or among) entity types
• Relationship Instances
• An association between (or among) entity
  instances, where each relationship instance
  includes exactly one entity from each
  participating entity type
• e.g. De Silva works for Personnel Department

11
Conceptual Design

• Relationships
• A Department has A Manager
• (Employee)
• An Employee manage A Department

12
one to one relationship
Department
Personnel
Sales
Manager (Employee)
De Silva
Dias
13

• A Department controls Many Projects
• A Project controlled by A Department

14

• An Employee works on Many Projects
• A Project has Many Employees

15

• An Employee supervised by An Employee
• An Employee supervise Many Employees

16

• An Employee has Many Dependants
• A Dependant belongs to An Employee

17
Q 33 Model Paper

• The database design process is best described by
• (a)
• Prospective database users are interviewed to
  understand and document their data requirements.
• Analyse the information from data view and the
  functional view.
• Produce a concise description of the data
  requirements of the users and include detailed
  descriptions of the data types, relationships and
  constraints.
• Map the database structure developed to a data
  model of a particular DBMS.
• Define structures that enable the database to be
  queried in an efficient manner.

18

• (b)
• Prospective database users are interviewed to
  understand and document their data requirements.
• Design the information from data view and the
  functional view.
• Produce a high level description of the structure
  of a database.
• Map the database structure developed to a data
  model of a particular DBMS.
• Determine index and hash file designs.

19

• (c)
• Prospective database users are interviewed to
  understand and document their data requirements.
• Produce a high level description of the structure
  of a database.
• Map the database structure developed to a data
  model of a particular DBMS.
• Normalise database structures. Implement the
  database structures using a particular DBMS.

20

• (d)
• Prospective database users are interviewed to
  understand and document their data requirements.
• Analyse the information from data view rather
  than functional view.
• Produce a concise description of the data
  requirements of the users and include detailed
  descriptions of the data types, relationships and
  constraints.
• Map the database structure developed to a data
  model of a particular DBMS.
• Define structures that enable the database to be
  queried in an efficient manner.

21

• (e)
• Prospective database users are interviewed to
  understand and document their data requirements.
• Design the information from data view rather than
  functional view.
• Produce a concise description of the data
  requirements of the users and include detailed
  descriptions of the data types, relationships and
  constraints.
• Map the database structure developed to a data
  model of a particular DBMS.
• Define structures that enable the database to be
  queried in an efficient manner.

22
Notations

• Entity
• Relationship
• Attribute

23
Relationship Types

• One to One
• One to Many
• Many to many

24
Cardinality

• Specifies the number of instances of one entity
  that can (or must) be associated with each
  instance of another entity
• Minimum Cardinality
• The minimum number of instances of one entity
  that may be associated with each instance of
  another entity
• e.g. the minimum dependants for an employee is
  zero

25
Cont

• Optional Participation
• when the number of participants in the
  relationship is zero
• Mandatory Participation
• when the number of participants in the
  relationship is one
• Maximum Cardinality
• The maximum number of instances of one entity
  that may be associated with a single occurrence
  of another entity
• e.g. an Employee can have insurance policies for
  at most two dependants (02)

26
Existence Conditions

• One to One
• One to Many
• Many to Many

27
Existence Condition
Dias
Perera
De Silva
ABC Ltd.
28
Existence Condition
Dias
Perera
De Silva
ABC Ltd.
29
Existence Condition
XYZ Ltd.
Dias
Perera
De Silva
ABC Ltd.
30
supervise
works
Department
Employee
manage
control
has
works on
Dependent
Project
31
Q 39 Model Paper

• Questions 39-44 are based on the following
  information.Consider the following description
  about a University Student Management System.
• At the time of the registration the following
  data about a student is recorded Students name,
  student number, current address and the faculty
  to which (s)he belongs.
• Each faculty within the University is described
  by a name, faculty code, office number, office
  phone and the dean of the faculty who is elected
  from the lecturers in the faculty.
• Each subject offered by the faculty has a subject
  name, subject title, subject number, number of
  hours, and offering department.

32

• The lecturers attached to the faculty are
  described by the name, UPF no, home address and
  the phone number.
• The system also records grades obtained by each
  student for each subject.
• A student may follow 2 or more subjects.
• A lecturer may teach more than one subject but a
  subject has to be taught by a single lecturer
  from the beginning to the end.

33
1
M
teaches
Lecturer
Subject
(b)
has
1
M
Faculty
Dean
(c)
M
1
boss
Dean
Lecturer
(d)
1
M
(e)
34
Attributes

• Attribute
• A property or characteristic of an entity type
  that is of interest to the organisation
• Simple Attribute
• An attribute that cannot be broken down into
  smaller components
• e.g. Emp No

Emp No
35

• Multi-valued Attribute
• An attribute that may take on more than one value
  for a given entity instance
• e.g. Employee Skills, Qualifications
• Composite Attribute
• An attribute that can be broken down into
  component parts
• e.g. Address (Street, City, State, Postal Code)
• Name (First Name, Middle Initials, Last Name)

Skills
36

• Stored Attribute
• An attribute whose valued is stored in the
  database
• Derived Attribute
• An attribute whose values can be calculated from
  related attribute values
• e.g. Years Employed (using Employed Date)
• Age (using Date of Birth)

Age
37
Q 20 2003 paper

• Consider the following diagram
• Which is a/are correct statement(s) with respect
  to information in the above diagram?

38

• (a) SSn is an entity of the Employee
  attribute.
• (b) Name is a composite attribute.
• (c) All attributes are single valued.
• (d) Age is a derived attribute.
• (e) Supervision relationship is a recursive
  relationship.

39
Identifier

• Identifier
• An attribute (or combination of attributes) that
  uniquely identifiers individual instances of an
  entity type
• e.g. Emp No
• Composite Identifier
• An identifier that consists of a composite
  attribute
• e.g. Flight Id (Flight No, Date)

40
Cont

• Choose an identifier that will not change its
  value over the life of each instance of the
  entity type
• Choose an identifier such that each instance of
  the entity type, the attribute is guaranteed to
  have valid values and not be null (or unknown)
• Avoid the use of so-called intelligent
  identifiers, whose structure indicates
  classifications, etc.
• Consider substituting single-attribute
  identifiers for large composite identifiers

41
Detailed Conceptual Design
Dept No unique identifier of a dept. Identifier De
pt Name name of a department Unique Location loca
tion of a department Multi-
Valued
Phone phone no. of a department Employees no.
of employees in a dept. Derived
42
Detailed Conceptual Design
Employee
Emp No unique identifier of an emp. Identifier Emp
Name name of an employee Composite First
Name first name of an employee Mid
Initials middle initials of an employee Last
Name last name of an employee NID national id of
an employee Unique Address address of an
employee Salary salary of an employee Gender sex
of an employee DOB birth date of an employee
43
Detailed Conceptual Design
44
(No Transcript)
45
Entity Types

• Strong (Regular) Entity
• An entity that exists independently of other
  entity types
• Weak Entity
• An entity types whose existence depends on some
  other entity

Employee
Dependent
46

• Identifying Owner
• The entity type on which the weak entity type
  depends
• e.g. Employee is the Owner of Dependent
• Identifying Relationship
• A relationship between a weak entity type and its
  owner

has
47
Conceptual Design with Weak Entities
48
Q 43- Model paper

• Which of the following concept(s) cannot be
  represented in the Enhanced Entity-Relationship
  model
• (a) Composite attribute
• (b) Derived attribute
• (c) Weak entity
• (d) Generalization relationship
• (e) Versions