The Relational DB Model

1
Lecture 3

• The Relational DB Model

2
Learning Objectives

• That the relational database model takes a
  logical view of data
• That the relational models 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
• Sqlgt ALTER USER username IDENTIFIED BY
  newpassword

5

• to get information on your table
• sqlgt DESC tablename
• to get a list of your tables
• Sqlgt SELECT TABLE_NAME FROM USER_TABLES
• To save
• Sqlgt commit
• To unsave
• Sqlgt rollback

6
Scope of SQL

• DDL statements
• SqlgtCreate
• SqlgtDrop
• SqlgtAlter
• DML statements
• SqlgtSelect
• SqlgtUpdate
• SqlgtDelete
• SqlgtInsert

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

• 11
• 1m
• Mn
• recursive

19
ER Diagrams and their conversion to relations

• 11
• 1m
• Mn

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 metadatadata about data
• Sometimes described as the database designers
  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