Database I Lecture 7 2 Previous Lecture Database

Database I Lecture 7

2 Previous Lecture Database installation Creating user Database Language RUR rafiu. af@gmail. com

3 Outline The Relational Model • Brief history of relational model • Terminologies • Integrity constraints • Views RUR rafiu. af@gmail. com

The Relational Model Chapter 3

5 History of Relational Model Proposed by E. F. Codd in 1970 A relational model of data for large shared data banks RUR rafiu. af@gmail. com

6 Examples of RDBMSs Oracle Microsoft SQL Server Microsoft Access Visual Fox. Pro RUR rafiu. af@gmail. com

7 Relational Model Based on mathematical concept of a relation • Which is physically represented as a table Codd, a trained mathematician, used terminology taken from mathematics, principally set theory and predicate logic RUR rafiu. af@gmail. com

8 Relational Model Terminology A relation is a table with columns and rows Only applies to logical structure of the database (external and conceptual level of ANSISPARC architecture), not the physical structure Attribute is a named column of a relation Domain is the set of allowable values for one or more attributes RUR rafiu. af@gmail. com

9 Relational Model Terminology. . Tuple is a row of a relation Degree is the number of attributes in a relation Cardinality is the number of tuples in a relation Relational Database is a collection of normalized relations with distinct relation names RUR rafiu. af@gmail. com

10 Instances of Sample Relations RUR rafiu. af@gmail. com

11 Mathematical Relations Consider two sets, D 1 & D 2, • Where D 1 = {2, 4} and D 2 = {1, 3, 5} Cartesian product, D 1 x D 2 is, • Set of all ordered pairs, where first element is member of D 1 and second element is member of D 2 o D 1 x D 2 = {(2, 1), (2, 3), (2, 5), (4, 1), (4, 3), (4, 5)} • Alternative way is to find all combinations of elements with first from D 1 and second from D 2 RUR rafiu. af@gmail. com

12 Properties of Relations Relation name is distinct from all other relation names in relational schema Each cell of relation contains exactly one atomic (single) value Each attribute has a distinct name Values of an attribute are all from the same domain RUR rafiu. af@gmail. com

13 Properties of Relations. . Each tuple is distinct; there are no duplicate tuples Order of attributes has no significance Order of tuples has no significance, theoretically RUR rafiu. af@gmail. com

14 Relational Keys Superkey Candidate key Primary key Alternate key Foreign key RUR rafiu. af@gmail. com

15 Superkey An attribute, or set of attributes, that uniquely identifies a tuple within a relation A super key may contain additional attributes that are not necessary for unique identification Consider Branch relation: {branch. No, street, city, postcode} {branch. No, street, city} {branch. No, city} or {branch. No, postcode} RUR rafiu. af@gmail. com

16 Candidate Key Superkey (K) such that no proper subset is a superkey within the relation In each tuple of R, values of K uniquely identify that tuple (uniqueness) In simple words, superkey with minimal attributes Simple key or composite key Consider Branch relation branch. No or postcode RUR rafiu. af@gmail. com

17 Primary Key Candidate key selected to identify tuples uniquely within relation Consider Branch relation Primary key: branch. No RUR rafiu. af@gmail. com

18 Alternate Keys Candidate keys that are not selected to be primary key Consider Branch relation Alternate key: postcode RUR rafiu. af@gmail. com

19 Foreign Key Attribute, or set of attributes, within one relation that matches candidate key of some (possibly same) relation Used to represent relationship In the Branch relation, branch. No is the primary key However, in the Staff relation the branch. No attribute exists to match staff to the branch office they work in In the Staff relation, branch. No is a foreign key branch. No in the Staff relation targets the primary key attribute branch. No in the home relation, Branch RUR rafiu. af@gmail. com

20 Representing Relational Database Schemas Conceptual schema or conceptual model Branch (branch. No, street, city, postcode) Staff (staff. No, Name, position, sex, DOB, salary, branch. No) RUR rafiu. af@gmail. com

Integrity Constraints 21 We need to understand Nulls before starting discussion on integrity constraints Null Represents value for an attribute that is currently unknown/not applicable for tuple Deals with incomplete or exceptional data RUR rafiu. af@gmail. com

22 Types of Integrity Constraints Entity integrity constraint Referential integrity constraint RUR rafiu. af@gmail. com

23 Entity Integrity Constraint In a base relation, no attribute of a primary key can be null RUR rafiu. af@gmail. com

24 Referential Integrity Constraint If foreign key exists in a relation, either foreign key value must match a candidate key value of some tuple in its home relation or foreign key value must be wholly null RUR rafiu. af@gmail. com

25 Example: Referential Integrity For Example: branch. No in Staff relation is foreign key from Branch relation It should not be allowed to create Staff record with branch number B 025, unless there exists a record for branch number B 025 in Branch relation However, we should be able to create new Staff record with null branch number (new Staff has joined the company but not assigned to particular branch yet) RUR rafiu. af@gmail. com

26 Violation of Referential Integrity RUR rafiu. af@gmail. com

Views in Relational Model 27 Base Relation Named relation corresponding to an entity in conceptual schema, whose tuples are physically stored in database View The dynamic result of one or more relational operations operating on the base relations to produce another relation RUR rafiu. af@gmail. com

28 Views A virtual relation that does not necessarily actually exist in the database but is produced upon request, at time of request Contents of a view are defined as a query on one or more base relations Views are dynamic, meaning that changes made to base relations that affect view attributes are immediately reflected in the view RUR rafiu. af@gmail. com

29 Purpose of Views Provides powerful and flexible security mechanism by hiding parts of database from certain users Permits users to access data in a customized way, so that same data can be seen by different users in different ways, at same time Can simplify complex operations on base relations RUR rafiu. af@gmail. com