1.1

Databases RevisionDatabases Revision

1.2

Database and DBMSDatabase and DBMS

Database: Set of contiguous data that is connected together using relations, and stored without redundancy.

DBMS

DBMS Users: DBA: define S.W. requirements, Maintainability, Consistency,

Security, Authorities, Monitoring. DB Designer: To define tables and relationships without

redundancy DB Programmer: To create DB, forms, reports,…etc. Users.

1.3

DB GenerationsDB Generations

First Generation: Tree Data Model and Network Model.

Second Generation: Relational Data Model. Based on relational algebra

Some data types could not be represented (Multimedia)

Third Generation: Enhanced RDBS.

Fourth Generation: Object Oriented Model (OMT).

1.4

DB KeysDB Keys

Super Key: Set of columns each of which has a unique data.

Candidate Key: Any one of the super key elements.

Primary Key: The selected candidate key for some table.

Composite Key: The primary key is composed of more than one field.

Foreign Key: Refers to the primary key of other table.

1.5

Entity-Relationship ModelEntity-Relationship Model

DB Model: Tool to describe data, relationships, semantics, and constraints of a DB.

We use Entity Relational Model with relational databases.

ER Model lies in the conceptual layer.

1.6

Entity-Relationship ModelEntity-Relationship Model

Example:

Degree of depositor relationship = 2

Degree of Entity account = 2

Degree of Entity customer = 4

1.7

Entity-Relationship ModelEntity-Relationship Model

The Schema: The logical structure of the database. (Tables, relations,…etc)

The Instance: Content of DB at some time.

Domain: The set of permitted values for each attribute.

Cardinality: The number of entities in the entity set that another entity can be associated with using relationship.

One to One

One to Many

Many to Many

1.8

Entity-Relationship ModelEntity-Relationship Model

Rectangles represent entity sets.

Diamonds represent relationship sets.

Lines link attributes to entity sets and entity sets to relationship sets.

Ellipses represent attributes

Double ellipses represent multivalued attributes.

Dashed ellipses denote derived attributes.

Underline indicates primary key attributes (will study later)

1.9

ExampleExample

1.10

RolesRoles

Entity sets of a relationship need not be distinct The labels “manager” and “worker” are called roles; they specify how

employee entities interact via the works-for relationship set.

Roles are indicated in E-R diagrams by labeling the lines that connect diamonds to rectangles.

Role labels are optional, and are used to clarify semantics of the relationship

1.11

Cardinality ConstraintsCardinality Constraints

We express cardinality constraints by drawing either a directed line (), signifying “one,” or an undirected line (—), signifying “many,” between the relationship set and the entity set.

E.g.: One-to-one relationship: A customer is associated with at most one loan via the relationship

borrower

A loan is associated with at most one customer via borrower

1.12

One-To-Many RelationshipOne-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower, a customer is associated with several (including 0) loans via borrower

1.13

Many-To-One RelationshipsMany-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower, a customer is associated with at most one loan via borrower

1.14

Many-To-Many RelationshipMany-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

1.15

Participation of an Entity Set in a Participation of an Entity Set in a Relationship SetRelationship Set

Total participation (indicated by double line): every entity in the entity set participates in at least one relationship in the relationship set

E.g. participation of loan in borrower is total

every loan must have a customer associated to it via borrower

Partial participation: some entities may not participate in any relationship in the relationship set

E.g. participation of customer in borrower is partial

1.16

Weak Entity SetsWeak Entity Sets

An entity set that does not have a primary key is referred to as a weak entity set.

The existence of a weak entity set depends on the existence of a identifying entity set it must relate to the identifying entity set via a total, one-to-many

relationship set from the identifying to the weak entity set

Identifying relationship depicted using a double diamond

The discriminator (or partial key) of a weak entity set is the set of attributes that distinguishes among all the entities of a weak entity set.

The primary key of a weak entity set is formed by the primary key of the strong entity set on which the weak entity set is existence dependent, plus the weak entity set’s discriminator.

1.17

Weak Entity Sets (Cont.)Weak Entity Sets (Cont.) We depict a weak entity set by double rectangles.

We underline the discriminator of a weak entity set with a dashed line.

payment-number – discriminator of the payment entity set

Primary key for payment – (loan-number, payment-number)

1.18

Database IntegrityDatabase Integrity

DB Integrity: Accuracy and Correctness of Data DB Integrity Types:

Domain Integrity Constraint:– CREATE DOMAIN stno AS CHAR(5)

SUBSTR(stno,1,1) = ‘s’ AND IS- NUMERIC(SUBSTR(stno,2,4)) – CREATE DOMAIN location AS CHAR(15)

location in( ‘Irbid’ , ‘Amman’ , ‘Aqaba’);– CREATE DOMAIN stno AS INTEGER(3)

stno > 0 Domain Forms Integrity:

– Telephone Number Field. Domain Range:

– Grade field must be between 0 and 100 Entity Integrity: Any primary key in some entity must not be null Referential Integrity:

– Do not add to detailed table till you add to the master one first.– Do not delete from master table until you delete from detailed one.