The Relational Model and DBMS Architecture

Submission of team names, tech, and ideas today.

Specification for next step will be uploaded to Moodle shortly in sha’ Allah.

Read it carefully and follow all the steps.

Pay attention to the deadline.

Submission through Moodle.

CLO1: Define the "database”, its architecture, the main concepts and characteristics of databases, as well as concepts related to the design, and implementation of a relational databases.

(Knowledge & Understanding) Chapter objectives:

 Compare logical and physical data independence.

 Define the components of relational model: tables, rows,

columns, etc.

 Briefly describe DBMS languages.

 Learn about database language terms.

 Learn about relational database constraints.

 Learn the difference between database architectures.

Why do we need it?



Agree on structure of the database before deciding on a particular implementation.

Consider issues such as:



What entities to model



How entities are related



What constraints exist in the domain



How to achieve good designs

ER diagrams can be translated to relational schema. We will see how.

We will start translating ER diagrams to SQL very soon.

 We want to store our data without worrying about

files on disk.

 We want to query (retrieve) our data.

 We want a representation that is easy to

understand.

 We want to easily map our ER diagrams.

 We want this to be separate from application code.

We will briefly look at DBMS languages:

 DDL  DML  DCL  TCL  We will cover DDL in the next chapter, then DML in the one after.

 Relational database: A set of relations.

 Relation: 2 parts:

 Schema: The name of relation, and name and type of attributes.

 Example: Employee (ID: string, name: string, dob: date).

 Instance: A table with rows (tuples) and columns.

 Number of rows is cardinality.

 Number of columns is degree or arity.

 Relational database schema: collection of all schemas.

 Database instance: collection of instances of all relations.

The Schema (or description) of a Relation:



Denoted by R(A, A, .....A)

1 2

n



R is the name of the relation

 attributes of the relation are A, A

The

, ..., A

1 2

n

 is in D, which is the domain of the i-th attribute.

A

i

i

 domain also contains null which is a special value that indicates

A unknown value.

 , ..., A are attributes with domains D ,..., D then (A :D, ..., A :D

If A

n

i

n

1 1

n

) is a relation schema that defines a relation type. Sometimes the

n domains are not included.

Ordering of tuples in a relation r(R):

 The tuples are not considered to be ordered, even if they appear to be in the

tabular form.

Ordering of attributes in a relation schema R (and of values within each tuple):

 We will consider the attributes in R(A1, A2, ..., An) and the values in t=<v1, v2, ...,

vn> to be ordered.

 (However, a more general alternative definition of relation does not require this

ordering. It includes both the name and the value for each of the attributes ).

 Example: t= { <name, “John” >, <SSN, 123456789> }  This representation may be called as “self-describing”.

Notation:



We refer to component values of a tuple t by:

 ] or t.A

t[A

i

i



This is the value v of attribute A for tuple t

i

i



Similarly, t[A, A, ..., A ] refers to the sub-tuple of t containing the

u v

w

values of attributes A, A, ..., A, respectively in t

u v

w

Sli

is

C O N S T R A I N T S Include:



Domain constraints.



Key constraints.



Constraints on nulls.



Entity integrity constraints.



Referential integrity constraints.

They specify that within each tuple, the value of each attribute must be an atomic value from the domain.

The data types associated with domains includes:

integers, real numbers, characters, Booleans, fixed length strings, variable length strings, date, time, timestamp...etc.

Other possible domains can be a range of values from a data type or as an enumerated data type in which all possible values are explicitly listed.

Specifies whether NULL values are or are not permitted e.g.if every STUDENT tuple must have a valid, non-NULL value for the Name attribute, then the Name of STUDENT is constrained to be NOT NULL.

Example: Consider the CAR relation schema:

 CAR(State, Reg#, SerialNo, Make, Model, Year)  CAR has two keys:

 Key1 = {State, Reg#}  Key2 = {SerialNo}

 Both are also superkeys of CAR  {SerialNo, Make} is a superkey but not a key.

In general:

 Any key is a superkey (but not vice versa)  Any set of attributes that includes a key is a superkey  A minimal superkey is also a key

Department(Dept_num, Dept_name, Location, Manager_name)

• Write the key, candidate keys, and super-keys for the Department relation.
• Write an appropriate domain for each attribute of the Department relation.

S C H E M A Relational Database Schema:



A set S of relation schemas that belong to the same database.



S is the name of the whole database schema



S = {R1, R2, ..., Rn} and a set IC of integrity constraints.



R1, R2, ..., Rn are the names of the individual relation schemas within the database S

Following slide shows a COMPANY database schema with 6 relation schemas

A relational database state DB of S is a set of relation states DB = { r, r, ..., r } such that each r is a state of R and such that

1 2

m

i

i

the r relation states satisfy the integrity constraints specified in

i

IC.

A relational database state is sometimes called a relational database snapshot or instance.

We will not use the term

instance since it also applies to single

tuples.

A database state that does not meet the constraints is an invalid state

S T A T E Each relation will have many tuples in its current relation state The relational database state is a union of all the individual relation states Whenever the database is changed, a new state arises Basic operations for changing the database:



INSERT a new tuple in a relation



DELETE an existing tuple from a relation



MODIFY an attribute of an existing tuple

Next slide (Fig. 5.6) shows an example state for the COMPANY database schema shown in Fig. 5.5.

Tuples in the referencing relation R1 have attributes FK (called foreign key attributes) that reference the primary key attributes PK of the referenced relation R2.



A tuple t1 in R1 is said to reference a tuple t2 in R2 if t1[FK] = t2[PK].

A referential integrity constraint can be displayed in a relational database schema as a directed arc from R1.FK to R2.

Each relation schema can be displayed as a row of attribute names The name of the relation is written above the attribute names The primary key attribute (or attributes) will be underlined A A foreign key (referential integrity) constraints is displayed as a directed arc (arrow) from the foreign key attributes to the referenced table  Can also point the the primary key of the referenced relation for

clarity

Next slide shows the COMPANY relational schema diagram with referential integrity constraints

In case of integrity violation, several actions can be taken:

 Cancel the operation that causes the violation (RESTRICT or

REJECT option)

 Perform the operation but inform the user of the violation  Trigger additional updates so the violation is corrected (CASCADE

option, SET NULL option)

 Execute a user-specified error-correction routine

DELETE may violate only referential integrity:



If the primary key value of the tuple being deleted is referenced from other tuples in the database  Can be remedied by several actions: RESTRICT, CASCADE, SET NULL (see

Chapter 6 for more details)  RESTRICT option: reject the deletion  CASCADE option: propagate the new primary key value into the foreign keys of the

referencing tuples

 SET NULL option: set the foreign keys of the referencing tuples to NULL



One of the above options must be specified during database design for each foreign key constraint.



on delete set null.



on delete cascade

Delete the WORKS_ON tuple with Essn =‘999887777’ and Pno = 10 The seletion is acceptable and deletes exactly one tuple Delete the EMPLOYEE tuple with Ssn = ‘999887777’ This deletion is unacceptable, because there are tuples in WORK_ON that refer to this tuple. Hence if the tuple in EMPLOYEE is deleted, referential integrity violations will result.

Delete the EMPLOYEE tuple with Ssn = ‘333445555’ The deletion is unacceptable because the tuple involved is referenced by tuples from the EMPLOYEE, DEPARTMENT, WORKS_ON and DEPENDENT relation.

UPDATE may violate domain constraint and NOT NULL constraint on an attribute being modified Any of the other constraints may also be violated, depending on the attribute being updated:



Updating the primary key (PK):



Similar to a DELETE followed by an INSERT



Need to specify similar options to DELETE



Updating a foreign key (FK):



May violate referential integrity



Updating an ordinary attribute (neither PK nor FK):



Can only violate domain constraints

Consider the following relations for a database that keeps track of student enrollment in courses and the books adopted for each course:

STUDENT(SSN, Name, Major, Bdate) COURSE(Course#, Cname, Dept) ENROLL(SSN, Course#, Quarter, Grade) BOOK_ADOPTION(Course#, Quarter, Book_ISBN) TEXT(Book_ISBN, Book_Title, Publisher, Author) Draw a relational schema diagram specifying the foreign keys for this schema.

We have also looked at all possible constraints in the database.

We will look at some DBMS components next.

The database and DBMS catalog are usually stored on disk.

Access to the disk is controlled by the operating system (OS) which schedules disk read/write.

A higher-level, stored data manager module (component) controls access to DBMS information that is stored on disk, whether it is part of the database or the catalog.

DBMS has interfaces for the DBA staff who work on defining the database and tuning it by making changes to its definition using the DDL and other privileged commands.

Centralized DBMS:



Combines everything into single system including- DBMS software, hardware, application programs, and user interface processing software.



User can still connect through a remote terminal – however, all processing is done at centralized site.

Specialized Servers with Specialized functions 

Print server



File server



DBMS server



Web server



Email server

Clients can access the specialized servers as needed

Provide appropriate interfaces through a client software module to access and utilize the various server resources.

Clients may be diskless machines or PCs or Workstations with disks with only the client software installed.

Connected to the servers via some form of a network.



(LAN: local area network, wireless network, etc.)

Provides database query and transaction services to the clients Relational DBMS servers are often called SQL servers, query servers, or transaction servers Applications running on clients utilize an Application Program Interface (API) to access server databases via standard interface such as:



ODBC: Open Database Connectivity standard



JDBC: for Java programming access

The emergence of web changed the roles of client and server.

Common for Web applications Three-tier architecture is a well-established software application architecture that organizes applications into three logical and physical computing tiers: the presentation tier, or user interface; the application tier, where data is processed; and the data tier, where the data associated with the application is stored and managed.

The chief benefit of three-tier architecture is that because each tier runs on its own infrastructure, each tier can be developed simultaneously by a separate development team, and can be updated or scaled as needed without impacting the other tiers.

Intermediate Layer called Application Server or Web Server:



Stores the web connectivity software and the business logic part of the application used to access the corresponding data from the database server



Acts like a conduit for sending partially processed data between the database server and the client.

Three-tier Architecture Can Enhance Security:



Database server only accessible via middle tier



Clients cannot directly access database server



Clients contain user interfaces and Web browsers



The client is typically a PC or a mobile device connected to the Web

Faster development: Because each tier can be developed simultaneously by different teams, an organization can bring the application to market faster, and programmers can use the latest and best languages and tools for each tier.

Improved scalability: Any tier can be scaled independently of the others as needed.

Improved reliability: An outage in one tier is less likely to impact the availability or performance of the other tiers.

Improved security: Because the presentation tier and data tier can't communicate directly, a well-designed application tier can function as a sort of internal firewall, preventing SQL injections and other malicious exploits.

Presented Relational Model Concepts 

Definitions



Characteristics of relations

Discussed Relational Model Constraints and Relational Database Schemas 

Domain constraints



Key constraints



Entity integrity



Referential integrity

Described the Relational Update Operations and Dealing with Constraint Violations