Database Management Systems

1
Database Management Systems

• IT 2302

2
Todays Lesson

• LMS
• Detailed ER diagram

3
LMS

• 4 Quizzes
• From separate areas of the syllabus
• 2 Assignments
• ER diagram
• Normalization
• SQL

4
Quiz 1

• Introduction to DBMS
• Database Arch.
• Database Admin.
• Relational Data Model
• Concepts of keys
• Integrity rules

5
Eg-

• Functions of a DBA include
• (a) communicating with all computer users
• (b) classify user groups and provide
  authorization to access data elements
• (c) retrieve data and supply daily reports to
  the top management
• (d) install upgrades of database software
• (e) monitor systems performance and optimize

6
To answer Question-

• DBA and DA
• The different functions of each group

7
Quiz 2

• Database design process
• ER
• EER
• Mapping to Relational model

8
Eg-

• The following diagram shows instances of
  warehouses and their employees. The instance
  relationships shown are for the storekeeper(s)
  who are also employees of these warehouses.

9

• Warehouse
• Which of the following statement(s) is (are)
  true?

Employee
10

• (a) A warehouse has 1 or 2 storekeepers.
• (b) An employee is assigned to a single
  warehouse.
• (c) Some persons are not employees of a
  warehouse.
• (d) Some warehouses may not have storekeepers.
• (e) Some employees are not storekeepers.

11
To answer Question

• Entity / Entity Instances
• Relationship between entity Instances
• Different types of relationships possible

12
More

• Together with questions you will get a deadline
  for submission.
• Submit answers before deadline
• Results recorded in system
• Individual submissions
• Group submissions

13
Assignment 1

• Have given a description about a hospital
• Details about the way the Information system
  works is given
• Data recorded/available are mentioned
• Questions on Data recorded, ER diagrams and
  Relation.

14
Q1

• Identify the essential data elements to be
  recorded in order to facilitate the hospital
  information system.

15

• Surgeon sname, specialty, etc
• Patient pname, address, sex, dob, etc
• Room rtype, etc
• Theatre tno, etc
• Other Entities

16
Student answers

• Some entities were missing in certain cases eg-
• Room
• Some had identified the entities but not their
  attributes

17
Group Answers

• Same problems

18
Q2

• Draw an entity-relationship diagram with
  participation/existence conditions, to model the
  information requirements of the database. State
  any assumptions you have made.

19

• PaRo (1,1) (0,1)
• PaSu (1,1) (0,N)

Surgeon
Room
Patient
Theatre
20
Student Answers

• Constraints were not properly identified
• Constraints were not properly given
• Primary keys not properly given
• New primary keys defined
• Some had neglected the attributes

21
Group Answers

• Use the standard notations for the ER diagram

22
Q3

• Draw a functional dependency diagram (FDD) for
  the hospital information system. State any
  assumptions you have made.

23
sname
specialty, .
pname
address, sex, dob, .
24
Student Answers

• Most had not done this properly
• Have only identified the basic dependencies.

25
Group Answers

• Some had confused ER diagram with the Functional
  Dependency Diagram

26
Q4

• Using the FDD, derive a set of relations
  (relations with their attributes only) that are
  at least in 3rd normal form. Give reasons where
  necessary, as well as, identify the keys of these
  relations.

27

• surgeon (sname, etc)
• patient (pname, address, sex, dob, etc.)
• Etc

28
Student / Group Answers

• Have not done this step

29
Quiz 3

• Data Normalization process
• Relational Algebra
• Data views and security
• Emerging database tech.
• 4GL development Env.

30
Eg-

• Which of the following statement(s) is (are)
  correct with respect of external views?
• (a) Hide data from unauthorized users.
• (b) Permit the use of derive attributes.
• (c) Join attributes from several tables to
  create a new virtual table for a user.
• (d) Allows changing the unit of measurement of
  some stored data.
• (e) User level security can be implemented.

31
To answer Question

• What are views?
• Uses of Views.
• Security and Views

32
Quiz 4

• SQL
• CREATE
• SELECT
• INSERT
• UPDATE
• DELETE
• Etc

33
Eg-

• Which set(s) of key words cannot be used with
  SQL INSERT statement to enter data?
• (a) Insert, Select, Into, Set, Where
• (b) Insert, Into, Values, Copy
• (c) Select, Into, From, Where, Having
• (d) Into, Insert, Values, Null
• (e) From, Distinct, To, Insert, Having

34
To answer Question

• The structure of the INSERT statement
• Nested SQL statements

35
Assignment 2

• CREATE statements to create the database with
  constraints
• VIEWs
• Report Layouts
• SQL statements to retrieve data

36
Detailed ER diagram
37
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)

38
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

39
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
40
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
41
Detailed Conceptual Design
42
(No Transcript)
43
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
44

• 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
45
Conceptual Design with Weak Entities
46
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