lecture 3 the relational db model. learning objectives that the relational database model takes a...
Post on 27-Dec-2015
225 Views
Preview:
TRANSCRIPT
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 saveSql> commit;
To unsaveSql> rollback;
Scope of SQL
DDL statements:• Sql>Create• Sql>Drop • Sql>AlterDML 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)
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.
Foreign key
• Relates two tables
• An attribute in ONE table which relates PK in another table
• Candidate key
• Secondary key
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
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
top related