Week 2 Lecture 2

1
Week 2 Lecture 2

• Structure of a database

2
Structure of a database
External Schema
Conceptual Schema
Internal Schema
Physical Schema
3
External level

• Level visible to user
• Multiple views of the system
• e.g. View an order - see limited product and
  customer information
• Only the database Administrator may access the
  whole database at this level

4
EXTERNAL SCHEMA

• Each external view is defined by means of an
  external schema
• Provides definitions of each external view.
• Written in a Data Definition Language
• individual to the user
• accessed through a 3GL, a query language or a
  special purpose forms or menu-based language

5
Conceptual level

• CONCEPTUAL - represents the entire information
  content of the database
• Consists of multiple types of conceptual record.
  This level preserves the data independence of the
  database.
• CONCEPTUAL SCHEMA - defines each of the various
  types of conceptual record, in a conceptual Data
  Definition Language.

6
Internal level

• INTERNAL - a low-level representation of the
  entire database it consists of multiple
  occurrences of multiple types of internal record.
  It is the stored record, inasmuch as it contains
  all but the device-specific information on the
  storage of the database.
• PHYSICAL - the physical device and block
  addresses for each of the records.

7
Mappings

• Each level maps onto adjoining levels
• conceptual / internal mapping specifies how
  conceptual records and fields are represented at
  the internal level
• Changes can be made in the internal level
  without affecting the conceptual level
• external / conceptual mapping defines the
  correspondence between an external view and the
  conceptual view

8
DBMS - Database Management System

• software handling access to the database
• allows both the database administrator and all
  users the access to the database to which they
  are entitled

9
How requests are processed

• User issues request (e.g. through SQL)
• DBMS intercepts and analyses request
• DBMS inspects user's external schema, external to
  conceptual mapping, conceptual schema,
  conceptual to internal mapping and the storage
  structure definition.
• DBMS executes operations on stored database.

10
DATABASE ADMINISTRATOR (DBA)

• Decide on the storage structure and access
  strategy
• Liaise with the users
• Define security and integrity checks
• Define a backup and recovery strategy
• Monitor and respond to performance

11
Utilities used by the DBA

• Load routines
• Dump/Restore routines
• Reorganisation routines
• Statistics routines
• Analysis routines
• Data dictionary (containing METADATA, which gives
  data descriptions and mappings)

12
Building a conceptual schema

• Please note The diagrams in this presentation
  are drawn using a tool that you will not be
  using. You will be using ERWin.

13
Building tables

• Use primary keys
• Put the tables with ONLY primary keys in first.
• This is the first layer.
• Put the tables that reference those tables in
  next.
• This is the second layer.
• This layer uses the keys of the first layer as
  FOREIGN keys.
• The second layer cannot be placed until the first
  layer is complete.
• See the BUILDER example following

14
Required new data structure
15
Hierarchy of data structure
16
Please note

• To show self-joins, the staff table has since
  been amended
• alter table staff add reports_to number(7)
• alter table staff add constraint has_as_boss
  foreign key (reports_to) references
  staff(staff_no)
• The new structure is the same as the one in the
  builder schema link in your web page and webCT.

17
Layers of tables

• The tables Customer, Staff and Supplier have only
  primary keys. They are the foundation layer.
  Layer 1.
• The tables Corder, Stock and SOrder have foreign
  keys that only reference the foundation layer.
  They are Layer 2.
• COrderline and Sorderline depend on the tables in
  Layer 2. They are layer 3.

18
Analogous to building bricks
19
Layer 1
The customer table is added, with key CustomerId,
the Staff with key StaffNo and the Supplier with
key SupplierId.
20
Layer 2
The stock and the SOrder depend on the Supplier,
both having foreign key SupplierId. The COrder
depends on BOTH Staff and Customer, having
foreign keys StaffPaid, StaffIssued and
CustomerId.
21
Layer 3

• Both the supplier order line and the order line
  depend on the stock, having stockcode as a
  foreign key and part of their key.
• COrderline depends on COrder.
• Sorderline depends on SOrder

SOrderline
COrderline
Stock
COrder
SOrder
Customer
Supplier
Staff
22
The built database
23
Recap

• Look back at the blocks.
• The table creates are the structure or the
  framework - i.e. the architects drawing
• The inserts are like the bricks. You cannot
  insert into a table unless there is supporting
  data in the table on which it depends.
• Do
• Creates starting with the one(s) with no
  dependents
• Inserts starting with the one(s) with no
  dependents
• Deletes starting with the one(s) on which no
  other depends
• Drops starting with the one(s) on which no other
  depends

24
Relational database design

• The conceptual schema is built using CREATE TABLE
  commands.
• A relation is relational
• If and only if every non-key attribute
• is determined by
• the KEY
• the WHOLE KEY.
• and nothing but the KEY
• so help me CODD!
• Dr. E.F. Codd, an IBM researcher, first developed
  the relational data model in 1970

25
Bottom-up Approach to Data Modelling

• Objectives
• Define the purpose of normalisation
• Determinacy / Dependency
• Defining the Data Dictionary
• First Normal Form
• Second Normal Form
• Third Normal Form

26
Normalisation

• Normalisation provides algorithms for reducing
  complex data structures into simple data
  structures
• Concerned with
• tidying up the data so there is no data
  redundancy
• ensuring that data is grouped logically
• Bottom up approach - start with data items
• Codds Law is a set of rules which ensure that
  the data is grouped correctly
• A normal form is a convenient structure into
  which data can be organised

10
27
Concept of Determinacy and Dependency

• if A determines B - then B is dependent on A
• B is dependent on A if given a value for A, there
  is only one possible value for B
• e.g.
• student name is dependent on student number
• and
• student number determines student name

28
Data Dictionary

• central store of data that supports other models
• unambiguous and concise way of recording data
  about data (metadata)
• can be recorded manually or using a software tool
• encourages consistency between models by using
  same names in different models
• prevents duplication of data
• resolves problems of aliases which are all
  recorded against the appropriate data item
• built up as the models are developed
• aids communication as everyone on development
  team knows the exact meaning of words and terms
  used

29
Sequence, selection, iteration

• Sequence
• CustomerDetails Name Address PhoneNo
• Repetition
• Name Title Initial Surname
• Optionality ( )
• CustomerDetails Name Address (PhoneNo)
• Selection
• Name (Title) ForeName Initial
  Surname
• Values
• Title Dr Mr Mrs Ms
• Comments .

30
Example

• Full data dictionary entries for CustomerDetails
• Address AddressLine
• CustomerDetails Name Address (PhoneNo)
• Name (Title) ForeName Initial
  Surname
• Title Dr Mr Mrs Ms

4
31
Two Technique Approach to Data Modelling

• E-R diagramming to find and group all data items
• normalisation to ensure data items are grouped
  correctly

32
Before we begin

• We have already discussed
• Entities
• Attributes
• Values
• Data and Metadata
• We now take one of the documents and apply
• Unnormalised form rules (Data Dictionary format)
• First Normal Form rules
• Second Normal Form rules
• Third Normal Form rules