Transcript Lecture 3

Lecture 3
The Relational DB Model
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
Oracle log on procedures
• Can log from home
• Download putty shell
http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html
Two steps
• Log on to UNIX
• Log on to ORACLE
To change ORACLE Password:
Sql> ALTER USER username IDENTIFIED
BY newpassword;
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;
Scope of SQL
DDL statements:
• Sql>Create
• Sql>Drop
• Sql>Alter
DML statements
• Sql>Select
• Sql>Update
• Sql>Delete
• Sql>Insert
Creating DB Structure
Steps
• create SCHEMA
(already done for you)
• create TABLES
– FK & PK
• create VIEWS
• create indexes
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
Data Structure & Data Types
Data Structure:
• Domain
• Relationship
• relational DB
• Keys (PK & FK)
Data Type:
• Numeric/Character
• Date
• Logical
• Pictures
• Graphical
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)
Relational DB
• a collection of relations
Keys
• Primary
• Candidate
• Foreign
• Secondary
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.
PK for a Bank
CUSTOMER (CUST_ID, ACCT_ID, ACCT_BALANCE)
Foreign key
• Relates two tables
• An attribute in ONE table which relates PK
in another table
• Candidate key
• Secondary key
Functional dependency
A-- B
(A , B)-- C
Integrity Rules
• Entity integrity
• Referential integrity
Relational Algebra (p 72 & p298)
Union compatible
Operations
• Union
• Difference
• Intersect
Relationship among databases
•
•
•
•
1:1
1:m
M:n
recursive
ER Diagrams and their
conversion to relations
• 1:1
• 1:m
• M:n
The Data Dictionary
and System Catalog (page 78)
• Data dictionary
– Used to provide detailed accounting of all
tables found within the user/designercreated 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
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/designercreated table
Indexes
• Unique
• Non-unique
• Index points to the records in the table
• Q1,4, 5 and 6/p96