Lecture 3

1. Lecture 3 The Relational DB Model

2. Learning Objectives • That the relational database model takes a logical view of data • That the relational model’s basic components are entities, attributes, and relationships among entities • How entities and their attributes are organized into tables • About relational database operators, the data dictionary, and the system catalog • How data redundancy is handled in the relational database model • Why indexing is important

3. Oracle log on procedures • Can log from home • Download putty shell http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html

4. Two steps • Log on to UNIX • Log on to ORACLE To change ORACLE Password: Sql> ALTER USER username IDENTIFIED BY newpassword;

5. to get information on your table: sql> DESC tablename to get a list of your tables: Sql> SELECT TABLE_NAME FROM USER_TABLES; To save Sql> commit; To unsave Sql> rollback;

6. Scope of SQL DDL statements: • Sql>Create • Sql>Drop • Sql>Alter DML statements • Sql>Select • Sql>Update • Sql>Delete • Sql>Insert

7. Creating DB Structure Steps • create SCHEMA (already done for you) • create TABLES • FK & PK • create VIEWS • create indexes

8. Relational table • Two Dim structure • Order of rows is NOT important • Cols represent attributes • Row represent an occurrence of an entity • Each column has a set of allowable values, called domain • Each table has a primary key • Intersection of row/column represent a single value

9. Data Structure & Data Types Data Structure: • Domain • Relationship • relational DB • Keys (PK & FK) Data Type: • Numeric/Character • Date • Logical • Pictures • Graphical

10. Relation • Relation is a table of n columns and m rows., Referred to as: m x n • rows define CARDINALITY (m), cols define DEGREE (n) • relations are represented as: • relation (attribute names..) • ex; • STUDENT (student name, student ss#, student address, GPA)

11. Relational DB • a collection of relations Keys • Primary • Candidate • Foreign • Secondary

12. Primary Key (PK) (page 66) a unique identifier guarantees that each row of a relation can be uniquely addressed, in other words, if I give you the value of a primary key ,we should get one and only one tuple (row) from the table. It is usually a field from the table or a combination of fields (also called concatenated or composite key) from the table.

13. PK for a Bank CUSTOMER (CUST_ID, ACCT_ID, ACCT_BALANCE)

14. Foreign key • Relates two tables • An attribute in ONE table which relates PK in another table • Candidate key • Secondary key

15. Functional dependency A-- B (A , B)-- C

16. Integrity Rules • Entity integrity • Referential integrity

17. Relational Algebra (p 72 & p298) Union compatible Operations • Union • Difference • Intersect

18. Relationship among databases • 1:1 • 1:m • M:n • recursive

19. ER Diagrams and their conversion to relations • 1:1 • 1:m • M:n

20. The Data Dictionary and System Catalog (page 78) • Data dictionary • Used to provide detailed accounting of all tables found within the user/designer-created database • Contains (at least) all the attribute names and characteristics for each table in the system • Contains metadata—data about data • Sometimes described as “the database designer’s database” because it records the design decisions about tables and their structures

21. The Data Dictionary and the System Catalog (continued) System catalog • Contains metadata • Detailed system data dictionary that describes all objects within the database • Terms “system catalog” and “data dictionary” are often used interchangeably • Can be queried just like any user/designer-created table

22. Indexes • Unique • Non-unique • Index points to the records in the table

23. Q1,4, 5 and 6/p96