1 Relational Databases Model Terminology A relation is

1

Relational Databases 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. 2

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

Instances of Branch and Staff (part) Relations 4

Examples of Attribute Domains 5

Alternative Terminology for Relational Model 6

Database Relations Schema ◦ A chart of types of data ◦ Describes data by giving the name of the data item and its attributes. Relation schema ◦ Named relation defined by a set of attribute and domain name pairs. Relational database schema ◦ Set of relation schemas, each with a distinct name. ◦ A logical description of a relational database 7

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

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

Relational Keys • Primary Key • Candidate Key • Alternate Keys • Foreign Key – An attribute, or a set of attributes, that uniquely identifies a tuple within a relation. ◦ Any attribute that could serve as primary key ◦ Any attribute that is not a primary key or candidate key is called a nonkey attribute – A collection of candidate keys that are not selected to be primary key. – Attribute, or set of attributes in one relation that matches a primary key or candidate key in another relation (or in the same relation). 10

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 (i. e. a value that is not there) and is not the same as zero or spaces, which are values. 11

Relational Integrity 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 primary key or candidate key value of some tuple in another relation (or parent relation) or foreign key value must be entirely null. 12

Relational Integrity Enterprise Constraints ◦ Additional rules specified by users or database administrators. 13

14

Objectives of Three-Level Architecture • • • All users should be able to access same data. A user’s view is immune to changes made in other views. Users should not need to know physical database storage details. DBA should be able to change database storage structures without affecting the users’ views. Internal structure of database should be unaffected by changes to physical aspects of storage. DBA should be able to change conceptual structure of database without affecting all users. 15

Three-Level Architecture • A DBMS may be viewed from three levels of abstraction; External, and Conceptual & Internal 16

Three-Level Architecture External Level ◦ Users’ view of the database (what are the kind of facts to be added, read, or deleted; how they are presented) ◦ Describes that part of database that is relevant to a particular user (thus allowing the user to see the only data of interest ) ◦ User can be an application program or an end-user (DBA is a special case of user; may need to see the entire database) Conceptual Level ◦ Community view of the database (presents a logical view of the entire database as a unified whole) ◦ Describes what data is stored in database, relationships & constraints among the data. ◦ Created by a DDL ◦ Allows the DBMS to provide data independence ◦ Defines only content (storage or access details are ignored) 17

Three-Level Architecture Internal Level ◦ Physical representation of the database on the computer ◦ Describes how the data is stored in the database ◦ Provides an interface between the OS file system and the record structures used in higher levels (NB: a database is a collection of OS files on physical media) ◦ A level at which files, storage & and access methods, etc are defined 18

Differences between Three Levels of Architecture 19

Three-Level Architecture The three different levels of architecture (abstraction) allows: ◦ Changing the file organization (physical structure) without affecting the conceptual schema (view) ◦ Changing the conceptual schema without affecting the external schema (the application programs, user views) Net result is data independence 20

Logical and Physical Data The form in which the data is stored is not necessarily the same form in which it is presented to the application program The application programmer’s view of the data is much simpler than the actual data and is tailored to his own application The data structure which the application employs is called the logical structure The data structure which is actually stored on physical storage is called a physical structure Logical and physical are used to describe various aspects of the data 21

Logical and Physical Data Logical refers to the way programmers or end users see the data Physical refers to the way data is recorded on the physical storage media Logical data description is used to describe the programmer’s or end user’s view of the data Physical data description is used to describe the actual ways in which the data is stored It is the function of the software to convert the programmer’s descriptions to physical reality and vice-versa 22

Database Languages Data Definition Language (DDL) ◦ Allows the DBA or user to describe and name entities, attributes, and relationships required for the application ◦ plus any associated integrity and security constraints. 23

Database Languages Data Manipulation Language (DML) ◦ Provides basic data manipulation operations on data held in the database. Procedural DML ◦ allows user to tell system exactly how to manipulate data. Non-Procedural DML ◦ allows user to state what data is needed rather than how it is to be retrieved. 24

Database Languages Fourth Generation Language (4 GL) ◦ ◦ ◦ Query Languages Forms Generators Report Generators Graphics Generators Application Generators. 25

Data Model Integrated collection of concepts for describing data, relationships between data, and constraints on the data in an organization. Data Model comprises: ◦ a structural part; ◦ a manipulative part; ◦ possibly a set of integrity rules. 26

Data Model Purpose ◦ To represent data in an understandable way. Types of data models include: ◦ Relational Data Model ◦ Network Data Model ◦ Hierarchical Data Model 27

Hierarchical Data Model Basically a tree of linked records, i. e. records are arranged in a top-down structure that resembles a tree. Records are related by parent-child links (e. g. using pointers), where a parent may have many children but each child has only one parent (a nested one-tomany set of relationship). The top record has no parent and is called the root, the bottom records are called leaves and the intermediate records have one parent and several children. 28

Hierarchical Database Model 29

Network Data Model More complex than the hierarchical model Data are stored in records or as pointers Each record may be associated with an arbitrary number of records leading to many-to-many relationship Relationships can be implemented using pointers between related records in different files. Less flexible due to many access paths; there is significant overhead in storage space and maintenance. 30

Network Data Model 31

Relational Data Model Simpler data model than the hierarchical and network data models Consists of relations (tables), containing tuples (rows) and attributes (columns) All data are stored in relations 32

Relational Data Model 33

Conceptual Modeling Conceptual schema is the core of a system supporting all user views. Should be complete and accurate representation of an organization’s data requirements. Conceptual modeling is process of developing a model of information use that is independent of implementation details. Result is a conceptual data model. 34

System Catalog Repository of information (metadata) describing the data in the database. Typically stores: ◦ ◦ names of authorized users; names of data items in the database; constraints on each data item; data items accessible by a user and the type of access. Used by modules such as Authorization Control and Integrity Checker. 35