Databases F. Sadri Contents: First Half Introduction Relational - - PowerPoint PPT Presentation

▶
databases f sadri
SMART_READER_LITE
LIVE PREVIEW

Databases F. Sadri Contents: First Half Introduction Relational - - PowerPoint PPT Presentation

Feb 16, 2024 284 likes •689 views

Databases F. Sadri Contents: First Half Introduction Relational Databases Database Design (Normalisation) functional dependencies 1NF, 2NF, 3NF losslessness dependency preservation Boyce-Codd Normal Form 1 Contents cntd.

slide-1
SLIDE 1

1

Databases

  • F. Sadri

Contents: First Half

  • Introduction
  • Relational Databases
  • Database Design (Normalisation)

functional dependencies 1NF, 2NF, 3NF losslessness dependency preservation Boyce-Codd Normal Form

slide-2
SLIDE 2

2

Contents cntd.

  • Integrity
  • Database Design

(Semantic Modelling) Entity-Relationship Modelling

  • Introduction to Structured Query

Language (SQL)

slide-3
SLIDE 3

3

Contents: Second Half

  • Mr. James Jacobson

Introduction to SQL; Integrity Constraints; Transactions and their properties; Concurrency - Conflict serialisability, View serialisability; Locking and Deadlock; Timestamp Ordering; Recovery techniques

slide-4
SLIDE 4

4

Books

  • C.J. Date :

"An introduction to database systems," Addison-Wesley, 7th edition, 2000 and any later editions.

  • R. Elmasri, S.B. Navathe: “Fundamentals of

Database Systems”, Addison-Wesley, 3rd edition, 2000.

slide-5
SLIDE 5

5

  • A. Silberschatz, H.F. Korth, S. Sudarshan:

"Database system concepts," McGraw-Hill Book Company, 3rd edition, 1997.

  • T. Connoly, C. Begg, A. Strachan: “Database

Systems – A practical approach to design, implementation and management”, Addison Wesley, 2002 and any later editions.

slide-6
SLIDE 6

6

Database Systems

A database system is basically a computerised record-keeping system. Its

  • verall purpose is to maintain information,

and to make that information available on demand.

slide-7
SLIDE 7

7

Examples

  • They are everywhere!

Education Business Industry

slide-8
SLIDE 8

8

  • Company:

wages, products, stock control, customer records, employees records, accounts

  • Library: catalogue, lending records, purchases
  • Hospital: patient data, medical stocks
  • Banks
  • Local authorities voters registers
  • Tax registers
slide-9
SLIDE 9

9

(At the very least) A db system should provide facilities for users

  • To query the db to retrieve data
  • To process transactions that update the

database

  • To maintain the integrity and security of the

database

  • (In multi-user systems) To allow

concurrency, and provide some method of concurrency control

slide-10
SLIDE 10

10

Data Modelling

  • Hierarchical dbs
  • Network dbs
  • Relational dbs
  • Deductive dbs
  • Object oriented dbs
  • Object-relational dbs
  • Temporal dbs
  • etc
slide-11
SLIDE 11

11

Relational Databases

  • A relational database models the data as

a collection of tables (and nothing but tables).

  • The operators at the user's disposal (e.g.

for data retrieval and updates) are

  • perators that generate new tables from
  • ld.
slide-12
SLIDE 12

12

Examples

  • Postgres
  • Oracle
  • MySQL
slide-13
SLIDE 13

13

  • There is now a substantial theory for all

aspects of relational dbs from design to query evaluation to concurrency control. This theory assists in the optimal design of relational dbs and in the efficient processing

  • f user requests for information from the

database.

slide-14
SLIDE 14

14

  • Note:

The relational model we are describing here is a general abstract model. This model does not necessarily correspond in every last detail to any particular commercial product.

slide-15
SLIDE 15

15

Example The Suppliers-and-Parts Database

Table (Relation) S S# SNAME STATUS CITY S1 Smith 20 London S2 Jones 10 Paris S3 Blake 30 Paris S4 Clark 20 London S5 Adams 30 Athens

slide-16
SLIDE 16

16

Relation P P# PNAME COLOUR WEIGHT CITY P1 Nut Red 12 London P2 Bolt Green 17 Paris P3 Tube Blue 17 Rome P4 Tube Red 14 London P5 Pin Blue 12 Paris P6 Brace Red 19 London

slide-17
SLIDE 17

17

Relation SP (showing quantity

  • f parts supplied by suppliers)

S# P# QTY S# P# QTY S1 P1 300 S4 P2 200 S1 P2 200 S4 P4 300 S1 P3 400 S4 P5 400 S1 P4 200 S1 P5 100 S1 P6 100 S2 P1 300 S2 P2 400 S3 P2 200

slide-18
SLIDE 18

18

Terminology (Informal Definitions)

  • Relation:

A relation corresponds to a table. Each table has a unique name.

  • Tuple

Tuple: A tuple corresponds to a row of a table.

  • Attribute:

An attribute corresponds to a column of a table. A tuple would have a value for each attribute of the table.

slide-19
SLIDE 19

19

  • Domain:

A domain for an attribute is the set of all legal values of that attribute. Example: the domain of attribute CITY in relation S is the set of all legal city names, and the set of values appearing in attribute CITY of relation S at any given time is a subset of that set.

slide-20
SLIDE 20

20

Domain cntd.

Example: We may specify the domain of S# in relation SP as {S1, S2, S3, S4, S5, S6, S7, S8, S9, S10}. A value may appear in a domain, but not in the

  • relation. For example S5-S10 are in the domain of

S# for relation SP, but no suppliers S5-S10 actually appears in the current instance of that relation.

slide-21
SLIDE 21

21

  • In an good database system domains of each

attribute should be specified as part of the database definition, and used in the process of integrity checking.

  • Candidate Key:

A key is an identifier for the table. It is a column (or a combination of columns) with the property that, at any given time, no two rows of the table contain the same value in that column (or combination of columns). For example, in relation S both S# and SNAME are candidate keys.

slide-22
SLIDE 22

22

Summary of Terminology

Formal term Informal meaning Relation Table Tuple Row Attribute Column Domain Collection of legal values for a column Candidate Key Identifier

slide-23
SLIDE 23

23

Definition Relation scheme

A relation scheme describes the structure of a

  • relation. It corresponds to type definition in

programming languages. A relation scheme usually has a name and includes a list of attribute names. Example S (S#, SNAME, STATUS, CITY)

slide-24
SLIDE 24

24

More generally, a relation scheme can also specify the domain of the attributes. Example S (S# : S1, S2, S3, S4, S5, S6, S7, SNAME : string, STATUS : integer, CITY : string)

slide-25
SLIDE 25

25

A database scheme is the collection of the relation schemes for all the relations in that database. Example: The database scheme for the database consisting of relations S, P, SP (henceforth called the S-P-SP database) is

slide-26
SLIDE 26

26

S (S#, SNAME, STATUS, CITY) P (P#, PNAME, COLOUR, WEIGHT, CITY) SP (S#, P#, QTY)

slide-27
SLIDE 27

27

Database Relations

  • vs. Mathematical Relations
  • Tables: simple, intuitive notion
  • Direct correspondence between the concept of a

table and the mathematical concept of a relation.

  • It is from the mathematical concept of relation that

relational databases have inherited their name. The principles of the relational model were

  • riginally

laid down by E.F. Codd, a mathematician, who in the late 60's realised that the discipline of mathematics could be used to inject some solid principles and rigour into the field of database management and design, which up to that time, was deficient in such qualities.

slide-28
SLIDE 28

28

Revision

  • f some basic mathematical

concepts

Cartesian product of sets: Let A and B be sets. Then A ∗ ∗ ∗ ∗ B = {(a,b) : a ∈ ∈ ∈ ∈ A, b ∈ ∈ ∈ ∈ B}.

slide-29
SLIDE 29

29

In general, if Ai, 1 < i < n, are sets, then A1 ∗ ∗ ∗ ∗ A2 ∗ ∗ ∗ ∗ . . . *An = {(a1, a2, . . . , an) : ai ∈ ∈ ∈ ∈ Ai, 1 1 1 1 < < < < i < < < < n }.

slide-30
SLIDE 30

30

Example:

Suppliers = {Smith, Jones, Blake, Clark} Cities = {London, Paris, Athens} Suppliers ∗ ∗ ∗ ∗ Cities = {(Smith, London), (Smith, Paris), (Smith, Athens), (Jones, London), (Jones, Paris), . . . }

slide-31
SLIDE 31

31

A Relation on the sets A1 , A2 , . . . An (in the mathematical sense) is any subset of A1 ∗ A2 ∗ . . . ∗ An. The members of a relation are called n-tuples

  • r tuples, for short.
slide-32
SLIDE 32

32

Example: Suppliers-Cities, below, is a relation on Suppliers and Cities: Suppliers-Cities = {(Smith, London), (Jones, Paris), (Blake, Paris)}

slide-33
SLIDE 33

33

In tabular form: Supplier City Smith London Jones Paris Blake Paris

slide-34
SLIDE 34

34

Not every mathematical relation is a database

  • relation. In particular, composite domains

are not allowed in database relations. Composite Domain A composite domain is the Cartesian product

  • f some collection of simple domains.
slide-35
SLIDE 35

35

Example of a (mathematical) relation with a composite domain

EMPLOYEE-PROJECT EMP* EMP-NAME PROJECT E1 Smith CS 101 CS 203 EE 121 E2 Jones CS 202 CS 101 E3 Lee EE 410 This is not allowed as a database relation in the relational model.

slide-36
SLIDE 36

36

Consider the following two updates to relation EMPLOYEE-PROJECT: INSERT <E4, James, EE100> INSERT <E1, Smith, EE100>

slide-37
SLIDE 37

37

Properties of Database Relations

  • 1. There are no duplicate tuples.
  • 2. Tuples are unordered (top to bottom).
  • 3. All attribute values are atomic, i.e. non-
  • decomposable. So at every row-and-column

position within the table, there always exists precisely one atomic value, never a composite value, nor a set of values. Such a relation is said to be in first normal form.

slide-38
SLIDE 38

38

EMPLOYEE-PROJECT is not a db relation, according to our definition of db relations. It can easily be transformed into one, however. EMPLOYEE-PROJECT* EMP# EMP-NAME PROJECT E1 Smith CS 101 E1 Smith CS 203 E1 Smith EE 121 E2 Jones CS 202 E2 Jones CS 101 E3 Lee EE 410

slide-39
SLIDE 39

39

Table EMPLOYEE-PROJECT is unnormalised. Relation EMPLOYEE-PROJECT* is normalised,

  • r, more precisely, are in the first normal form.

It is a trivial task to transform an unnormalised table into an equivalent relation in first normal form.