DataBase Management 29

SCHEMAS AND STATE OF THE DATABASE

� Schema

� the description of a database

� specified during database design

� relatively stable over time

� Database state

� the data in a database at a particular moment

� the set of occurrences or instances

DataBase Management 30

DATABASE SYSTEM ARCHITECTURE

� Main characteristics of the database approach are:

i. Provision of data and program independence

ii. support of multiple user views

iii. use of a catalogue to store the database description.

� This is achieved by the Three-Schema Architecture.

� Internal (physical) level

o Describes physical storage structure of database

� Conceptual and logical levels

o Describes the structure of the whole database - describing entities, data types, relationships, user operations and constraints with no concern of the physical storage details

� External (view) level

o Describes the part of the database that a particular user group is interested in and hides the rest of the database from that user group.

DataBase Management 31

DATABASE SYSTEM ARCHITECTURE The Three-Schema Architecture

External view 1

Conceptual Schema

Internal Schema

External view 2

STORED DATABASE

Conceptual/internal mapping

External/conceptual mapping

END USERS

DataBase Management 32

DATABASE SYSTEMS DESIGN AND IMPLEMENTATION

USER REQUIREMENTS

CONCEPTUAL DESIGN

LOGICAL DESIGN

PHYSICAL DESIGN

IMPLEMENTATION

TESTING & INSTALLATION

ENGLISH DESCRIPTION

ER MODEL

DATA MODEL

FILE ANS INDEX DESIGN

TARGET DBMS SELECTION

DataBase Management 33

CONCEPTUAL DESIGN

� Input: requirements specification

� Output: conceptual schema

� Conceptual schema is a high level description of the structure of the database, independent of the particular DBMS software.

� A conceptual model is a language that is used to describe conceptual schemas.

� The purpose of conceptual design is to describe the information content of the database rather than the storage structures.

DataBase Management 34

LOGICAL DESIGN

� Input: conceptual schema

� Output: logical schema� Logical schema is a description of the structure of the database

that can be processed by the DBMS software.

� A logical model is a language that is used to specify logical schemas.

� Logical design depends on the class of data model used by the DBMS, but not on the specific DBMS used.

� Logical Models

o Relational Model

o Network Model

o Hierarchical Model

o Object-oriented

DataBase Management 35

PHYSICAL DESIGN

� Input: logical schema

� Output: physical schema� A physical schema is a description of the implementation of the

database in secondary memory.

� It describes the storage structures and access methods used in the system.

� There is feedback between physical and logical design, because decisions taken during physical design for improving performancemight affect the structure of the logical schema.

� Once the physical database design is completed, the logical and physical schemas are expressed using the data definition language of the target DBMS. The database is created and populated, and can be tested.

DataBase Management 36

DATA MODELING

� In designing a database for an enterprise, initially one has to identify and state what data needs to be stored – the data requirements.

� Example: Stock control application-

� Each stock item has a stock number, a description, price, quantity in stock, reorder level and supplier

� Each item has only one supplier

� From this statement of data requirements a conceptual data model is produced.

OPERATIONAL DATA = ENTITIES + RELATIONSHIPS

E.g. suppliers E.g. suppliers supplies a certain kind of part – hence link between supplier and part entities

DataBase Management 37

THE CONCEPTUAL DATA MODEL

� A conceptual data model describes how the data elements in the system are to be grouped. Three terms are used in building a picture of the data requirements.

� An entity is a thing of interest to an organization about which data is to be stored. - Example: stock, supplier, …

� An attribute is a property or characteristic of an entity. - Example: stock item description

� A relationship is a link or association between entities.- Example: link between stock items and suppliers: - one supplier may supply many stock items

� This conceptual/logical model is created without any regard to what type of database system will eventually be used to implement it.

Example: the entity stock and its attributes are expressed as follows:

STOCK(part_number, description, price, quantity, reorder level, supplier)

DataBase Management 38

TYPES OF RELATIONSHIPS

� Each relationship has a name (e.g. supplies) and the cardinality, which may be any of:

� one-to-one

� one-to-many (or many-to-one)

� many-to-many

� Example :- the relationship between supplier and stock:

� Name : supplies (as indicated)

� Cardinality: one-to-many

(a supplier supplies many stock items)

DataBase Management 39

OTHER EXAMPLES

� E.g. 1: One-to-one: a specialist is assigned to a single ward

(Given that a specialist has one ward of patients under his care)

� E.g. 2: One-to-many: a hospital ward occupied by many patients (but a patient is assigned to only one ward).

� E.g. 3: Many-to-many: a student may study several courses, each course has many students enrolled on it.

� Exercise: Identify the relationship between these entities:

� stock item and supplier

� CD and song

� Borrower and book

� Brother and Sister

� Bank Account and Customer

DataBase Management 40

ENTITY-RELATIONSHIP DIAGRAMS(E-R DIAGRAMS)

� An entity-relationship diagram is a diagrammatic way of representing the relationships between the entities in a database.

� To show the relationship between two entities, both the cardinality and the name of the relationship need to be specified.

� An E-R diagram illustrates the entities and their relationships for a specific system.

accommodates

SPECIALIST WARD

WARD PATIENTS

STUDENT COURSE

enrolls

visits

DataBase Management 41

EXAMPLE 1

� The data requirements for a hospital in-patient system are defined as follows:

� A hospital is organized into a number of wards. Each ward has a ward number and a name recorded, along with a number of beds in that ward. Each ward is staffed by nurses. Nurses have their staff number and name recorded, and are assigned to a single ward.

� Each patient in the hospital has a patient identification number, and their name, address and date of birth are recorded. Each patient is under the care of a single consultant and is assigned to a single ward. Each consultant is responsible for a number of patients. Consultants have their staff number, name and specialization recorded (Adapted from Heathcote).

1. State four entities for the hospital in-patient system and suggest an identifier for each of these entities.

2. Draw an entity-relationship diagram to show the relationship between the entities.

DataBase Management 42

SOLUTION

Staff NumberConsultant

Patient Identification NumberPatient

Staff NumberNurse

Ward NumberWard

Instance identificationENTITY

is assigned

visits

accommodates

PATIENT

WARD NURSE

CONSULTANT

DataBase Management 43

EXAMPLE 2

� Drawing an E-R diagram to represent this situation:

CUSTOMER ORDER

ORDER ITEM

relationship pairs

� In a mail order system, a customer may place an order for several items. Over a period of time, the same customer may place several orders.

� Entities: Customer, Item, Order

ORDER

CUSTOMER

ITEM

sends

is for

DataBase Management 44

Solution

� An alternative data model decomposing the many-to-many relationship between order and items:

ORDER

CUSTOMER

ORD_ITEM

sends

includes

ITEM

matches

� An order includes a number of ordered items. A particular item may be ordered in different orders (hence matches 0, 1 or many ordered item records) .

DataBase Management 45

Exercise

The data requirements for a booking system are defined as follows:

� An agency arranges booking of live bands for a number of clubs. Each band is registered with the agency and has its name (unique) recorded, together with the number of musicians, the type of music played and hiring fee. Each band is managed by a manager. A manager may manage several bands. Each manager is assigned an identification number and managers have their name, address and telephone number recorded. Each club is assigned an identification number and clubs have their name, address and telephone number recorded.

� The agency records details of each booking made between a band and a club for a given date. A band will never have more than one booking on any particular date.

1. State four entities for this booking system and suggest an identifier for each of these entities.

2. Draw an entity-relationship diagram to show the relationship between the entities.