Transcript Relational Database

Relational Database
Database Management System
(DBMS)
Database Approach
• Data definition language (DDL).
– Permits specification of data types, structures and
any data constraints.
– All specifications are stored in the database.
• Data manipulation language (DML).
– General enquiry facility (query language) of the
data.
Database Approach
• Controlled access to database may
include:
–
–
–
–
–
A security system.
An integrity system.
A concurrency control system.
A recovery control system.
A user-accessible catalog.
• A view mechanism.
– Provides users with only the data they want or need
to use.
Views
• Allows each user to have his or her own
view of the database.
• A view is essentially some subset of the
database.
Views
• Benefits include:
– Reduce complexity;
– Provide a level of security;
– Provide a mechanism to customize the appearance
of the database;
– Present a consistent, unchanging picture of the
structure of the database, even if the underlying
database is changed.
Practice
• Open Microsoft Access
• Open Example database
• Insert 3 records into the Department table
Dept Manager
Description
101 017-11-0031
102 018-21-2131
103 019-41-1231
Marketing
Accounting
Customer service
• Create a query to select all department
with Dept=“001”
Basics of the Relational Model
• The relational model represents information in
tables (called relations)
– Each table represents a set of entities
– Each column of a table represents the attribute
values of the entities
– Each row of a table represents a single entity
• The rows of a relational table are unique
• Every table must have a key
Relation Schemas and Keys
• A key declaration is a constraint
– A table is not allowed to have 2 different rows that
have the same value for the key
– Database systems enforce key constraints
• By blocking any attempt to modify a table that will result in a
violation of the key constraint
• A relation schema is the specification of the
structure of a table
– Name of the table
– Name and type of each attribute
– Declaration of the key
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 the set of allowable values
for one or more attributes.
Relational Model
Terminology
• Tuple is a row of a relation.
• Degree is the number of attributes in a
relation.
• Cardinality is the number of tuples in a
relation.
• Relational Database is a collection of
normalized relations with distinct
relation names.
Instances of Branch and
Staff (part) Relations
Examples of Attribute
Domains
Alternative Terminology for
Relational Model
Properties of Relations
• Relation name is distinct from all
other relation names in relational
schema.
• Each cell of relation contains exactly
one atomic (single) value.
• Each attribute has a distinct name.
• Values of an attribute are all from the
same domain.
Properties of Relations
• Each tuple is distinct; there are no
duplicate tuples.
• Order of attributes has no significance.
• Order of tuples has no significance,
theoretically.
Practice
• Inside Microsoft Access, and Example
database. Open the table Department (by
double click)
• Repeat the first row of the table
Department (creating duplication
tuples/record/row)
Relational Keys
• Primary Key
– Candidate key selected to identify tuples
uniquely within relation.
• Alternate Keys
– Candidate keys that are not selected to be
primary key.
• Foreign Key
– Attribute, or set of attributes, within one
relation that matches candidate key of some
(possibly same) relation.
Relational Integrity
• Null
– Represents value for an attribute that is
currently unknown or not applicable for
tuple.
– Deals with incomplete or exceptional data.
– Represents the absence of a value and is
not the same as zero or spaces, which are
values.
Relational Integrity
• Data integrity: state that said the data
contained in a database is correct
• Entity Integrity
– In a base relation, no attribute of a primary
key can be null.
• Referential Integrity
– If foreign key exists in a relation, either
foreign key value must match a candidate
key value of some tuple in its home relation
or foreign key value must be wholly null.
Relational Integrity
Relational Integrity
• Enterprise Constraints/User-defined
constraints
– Additional rules specified by users or
database administrators.
Views
• Base Relation
– Named relation corresponding to an
entity in conceptual schema, whose
tuples are physically stored in
database.
• View
– Dynamic result of one or more
relational operations operating on base
relations to produce another relation.
Relationship types
• One-to-One relationship: each record is
related to only one record in the related
table.
• One-to-Many relationship: each record
in a table can have multiple related
records
• Many-to-Many relationship: each record
in a table can be related to multiple
records in another table and vice versa.
Practice
• Create a table “Employee” with 3 fields:
– SSN: char (11)
– Name: char(100)
– Salary: double
Set SSN as the primary key
• Select Tools -> Relationships -> Select tables
Department -> Add, then Employee -> Add.
• In the Relationship window, draw a connection
from Employee table to Department table
Our Database
Book
ISBN
Title
Author
Publisher
AuthorId
PublisherId
Name
PublisherName
AuthorID
Genre
Year
PublisherID
Pages
Price
Cover
Subject
Website
User
UserId
Password
AddressStreet
AddressTown
Zipcode
Email
TransactionId
Transaction
TransactionId
UserId
BookId
Date
Status
Projects
• Project 1 (database)
– Part 1: create database, tables,
populate data, make links between
tables
– Part 2: modified existing database,
add more tables, links
– Part 3: query
– Part 4: database front-end (form)
– Part 5: advanced forms
– Part 6: reports