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

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. • Integrity • Database Design ( Semantic Modelling) Entity-Relationship Modelling • Introduction to Structured Query Language (SQL) 2

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 3

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, 3 rd edition, 2000. 4

• 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. 5

Database Systems A database system is basically a computerised record-keeping system. Its overall purpose is to maintain information, and to make that information available on demand. 6

Examples • They are everywhere! Education Business Industry 7

• 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 8

(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 9

Data Modelling • Hierarchical dbs • Network dbs • Relational dbs • Deductive dbs • Object oriented dbs • Object-relational dbs • Temporal dbs • etc 10

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 operators that generate new tables from old. 11

Examples • Postgres • Oracle • MySQL 12

• 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 of user requests for information from the database. 13

• 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. 14

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 15

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 16

Relation SP (showing quantity of 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 17

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. 18

• 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. 19

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. 20

• 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. 21

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

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) 23

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) 24

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 25

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

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 originally 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 27 up to that time, was deficient in such qualities.

Revision of some basic mathematical concepts Cartesian product of sets: Let A and B be sets. Then A ∗ ∗ B = {(a,b) : a ∈ ∈ A, b ∈ ∈ B}. ∗ ∗ ∈ ∈ ∈ ∈ 28

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

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

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 or tuples , for short. 31

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

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

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 of some collection of simple domains. 34

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. 35

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

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 . 37

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 38

Table EMPLOYEE-PROJECT is unnormalised . Relation EMPLOYEE-PROJECT* is normalised , or, 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. 39