Relational Database

1
Relational Database
2
Database Management System (DBMS)
3
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.

4
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.

5
Views

• Allows each user to have his or her own view of
  the database.
• A view is essentially some subset of the
  database.

6
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.

7
Practice

• Open Microsoft Access
• Open Example database
• Insert 3 records into the Department table
• Dept Manager Description
• 101 017-11-0031 Marketing
• 102 018-21-2131 Accounting
• 103 019-41-1231 Customer service
• Create a query to select all department with
  Dept001

8
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

9
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

10
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.

11
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.

12
Instances of Branch and Staff (part) Relations
13
Examples of Attribute Domains
14
Alternative Terminology for Relational Model
15
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.

16
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.

17
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)

18
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.

19
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.

20
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.

21
Relational Integrity
22
Relational Integrity

• Enterprise Constraints/User-defined constraints
• Additional rules specified by users or database
  administrators.

23
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.

24
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.

25
Practice

• Create a table Employee with 3 fields
• SSN char (11)
• Name char(100)
• Salary double
• Set SSN as the primary key
• Select Tools -gt Relationships -gt Select tables
  Department -gt Add, then Employee -gt Add.
• In the Relationship window, draw a connection
  from Employee table to Department table

26
Our Database
Publisher
Author
Book
ISBN Title AuthorID Genre Year PublisherID Pages P
rice Cover Subject
PublisherId PublisherName Website
AuthorId Name
User
Transaction
UserId Password AddressStreet AddressTown Zipcode
Email TransactionId
TransactionId UserId BookId Date Status
27
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