CS 370 Database Systems

1. CS 370 Database Systems Lecture 7 The Relational model CS370 Spring 2007

2. Table Name: EMP Table Name: DEPT EMPNO ENAME JOB DEPTNO 7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7566 JONES MANAGER 20 DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON Definition of Relational Database • A relational database is a collection of relations or two-dimensional tables. Database CS370 Spring 2007

3. Relational Database • A relational database is a collection of data items organized as a set of formally-described tables from which data can be accessed or reassembled in many different ways without having to reorganize the database tables. • The relational database was invented by E. F. Codd at IBM in 1970. CS370 Spring 2007

4. Relational Database • A relational database is a set of tables containing data fitted into predefined categories. • Each table (which is sometimes called a relation) contains one or more data categories in columns. • Each row contains a unique instance of data for the categories defined by the columns. CS370 Spring 2007

5. Relational Model Terminology • A relation is a table with columns and rows. Only applies to logical structure of the database, not the physical structure. - Attribute is a named column of a relation. - Domain is a set of allowable values for one or more attributes. - Tuple is a row of a relation. - Degree is a number of attributes in a relation. - Cardinality is a number of tuples in a relation. • Relational Database is a collection of normalized relations. CS370 Spring 2007

6. Relational Model Terminology • Table • - Stores data about a particular topic or subject • - The basic element in any database • Organized in • - Rows represent the records in a database • - Columns represents the fields in a database • - First Row represents the field names CS370 Spring 2007

7. An ExampleRelation Relation Name/Table Name Attributes/Columns (collectively as a schema) Tuples/Rows CS370 Spring 2007

8. Relation - A two-dimensional table with the following properties: 1) the entries in the table are single-valued 2) each column has a distinct name (attribute name) 3) all values in a column are values of the same attribute 4) the order of the columns is unimportant 5) each row is distinct 6) the order of the rows is unimportant Normalized Relation - A Structure that satisfies all of these properties. CS370 Spring 2007

9. Example of an “Un-Normalized" relation: - A relational database is a collection of relations. • Rows are called tuples • columns are called attributes CS370 Spring 2007

10. Alternative Terminology for Relational Model CS370 Spring 2007

11. Relation • Relation, table, file • A two-dimensional table consisting of columns and rows; created • from the entities of the object model. • Attribute, column, field • The columns of the table, usually defined from the attributes of the object model. • Tuple, row, record • The rows of the table; derived from the entity occurrences of the object model. CS370 Spring 2007

12. Key - A key is an attribute that appears in a database "record" (a table row) that is used to distinguish that entity instance, meaning that particular row in the table. • A key is an identifying property of a model and/or a database. • Keys are a way to associate tuples in different relations (i.e., • primary, candidate, foreign). CS370 Spring 2007

13. FLIGHT# AIRPORT-CODE FLIGHT# AIRPORT-CODE FLIGHT# AIRPORT-CODE FLIGHT# AIRPORT-CODE FLIGHT-SCHEDULE DEPT-AIRPORT FLIGHT# AIRLINE WEEKDAY PRICE FLIGHT# AIRPORT-CODE 101 delta mo 156 101 atl 545 american we 110 912 cph 912 scandinavian fr 450 545 lax 242 usair mo 231 242 bos Keys and Identifiers Keys (or identifiers) are uniqueness constraints • A key onFLIGHT# in FLIGHT-SCHEDULE will force allFLIGHT#’s to be unique inFLIGHT-SCHEDULE • Consider the following keys onDEPT-AIRPORT: CS370 Spring 2007

14. Primary Key • The primary key is important since it is the sole identifier • for the tuples in a relation. • Any tuple in a database may be identified by specifying • relation name,primary key and its value. • A primary key is composed of one column (simple primary • key) or a combination of columns (composite primary keys) • that provide this unique identification. CS370 Spring 2007

15. Primary Key • The relational data model therefore imposes • the following two integrity constraints: • No component of a primary key value can be null. • (b) Attempts to change the value of a primary key • must be carefully controlled. CS370 Spring 2007

16. Candidate Key • A candidate key is a unique identifier that might be • considered when choosing the primary key of a • relation. • Candidate keys can also be simple or composite. • Usually, one candidate key is designated as a primary • key, while the others may be called alternate keys. CS370 Spring 2007

17. ELEMENTS E_NAME E_SYMBOL E_ATOMIC_NO LEAD Pb 32 HYDROGEN H 1 HELEAM He 2 SODIEM Na 11 PK CK AK CS370 Spring 2007

18. Foreign Key -- If a foreign key F in relation R matches the primary key P of relation S than every value of F must either be equal to a value of P or be wholly null. -- You can logically relate data from multiple table using foreign key (FK). CS370 Spring 2007

19. CS370 Spring 2007

20. Suppliers – and – Parts Database SUPPLIERS S# SNAME STATUS CITY S1 SMITH 20 LONDON S2 ALI 10 LAHORE S3 ADAMS 20 PARIS PK PARTS P# COLOR WEIGHT# CITY P1 GREEN 12 LONDON P2 RED 10 LAHORE P3 BLUE 14 PARIS PK S# P# QTY S_P S1 P1 300 S2 P2 259 S3 P3 400 CS370 Spring 2007