Week 2 Lecture 2 - PowerPoint PPT Presentation

1 / 32
About This Presentation
Title:

Week 2 Lecture 2

Description:

Week 2 Lecture 2 Structure of a database – PowerPoint PPT presentation

Number of Views:106
Avg rating:3.0/5.0
Slides: 33
Provided by: pobyrne
Category:

less

Transcript and Presenter's Notes

Title: 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
Write a Comment
User Comments (0)
About PowerShow.com