Dr Hassan TOUT Office 234 Office Hours Day

Dr. Hassan TOUT Office: 234 Office Hours Day Time Tuesday 11: 30 – 13: 00 Friday 11: 30 – 13: 00 This course is available from internet: http: //coursefs 1. ul. edu. lb Intranet: http: //172. 16. 2. 198 Data. Base Faculty of Sciences I 2008 -2009

Textbooks n Database management Systems, Raghu Ramakrishman / Johannes Gehrke, MC GRAW HILL n Fundamentals of DATABASE SYSTEMS, ELMASRI / NAVATHE, ADDISONWESLEY 2008 -2009 Data. Base H. TOUT 2

Outline Introduction n Entity/Relationships model n The relational model n Normal Forms n The SQL query language n 2008 -2009 DATABASE H. TOUT 3

Chapter I INTRODUCTION 2008 -2009 Data. Base H. TOUT 4

Outline Intuitive Approach n Definitions n The 3 Data. Base System (DBS) functions n Data description n Data storing and management n Data. Base control n n Example of DBSs 2008 -2009 Data. Base H. TOUT 5

INTUITIVE APPROACH n n Data. Base: collection of data describing the activities of an organization. For example, university’s Data. Base might contain information about: n n n Students, Faculties, Courses, Classrooms, Students’ enrollment in courses, The use of rooms for courses. 2008 -2009 DATABASE H. TOUT 6

Why Data. Base is important? n Organization’s success depends on: n Its ability to acquire accurate and timely data about its operations, n Its ability to manage this data effevtively, n Its ability to use it to analyse and guide its activities. n Need for powerfull and flexible data management system. Data. Base Management System (DBMS) Data. Base System (DBS) 2008 -2009 DATABASE H. TOUT 7

Data. Base System (DBS) n Software designed to assist in maintaining and utilizing large collections of data. Data. Base System Application Storing data 2008 -2009 DATABASE H. TOUT DB 8

Data. Base System (DBS) Data. Base System Application DB Extracting data 2008 -2009 Data. Base H. TOUT 9

Example of DBS n Micro : n ACCESS n PARADOX n DBASE V n FOXPRO, . . . DB 2 n ORACLE n SQL server n SYBASE n INGRES. . . n 2008 -2009 Big systems: n Data. Base H. TOUT 10

Need for a DBS Motivating example: A company C has a large collection of data about its employees, departments, products, sales, … this data is accessed concurrently by several employees: 1. Changes made to the data by different users must be applied consistently. For example, the DOB of an employee can’t be upper than its date of death. A DBS can enforce integrity constraints on the data 2. Access to certain parts of the data must be restricted. An employee is not allowed to modify its salary. A DBS can enforce access controls that govern what data is visible (accessible) to different classes of user 2008 -2009 DATABASE H. TOUT 11

Need for a DBS (2) 3. Data may be large and questions about the data must be answered quickly. A DBS utilizes a variety of sophisticated techniques to store and retrieve data efficiently 4. Data may be accessed by several users concurrently. A DBS schedules concurrent accesses to the data in such manner that users can think of the data as being accessed by only one user at time 5. System failures happen A DBS protects users from the effects of system failures 6. Application programs (that use the data) should be as independent as possible from details of data representation and storage A DBS can provide an abstract view of the data to insulate application code from such details 2008 -2009 DATABASE H. TOUT 12

Data Model and Semantic Data Model n n n In a DB, the structure of the data is described in terms of a Data Model and the description is called Schema Most DBS today are based on the relational data model. Data model is not very easy to use by a user because it does not reflect the thinks of a user about the underlying enterprise. A semantic data model is more abstract, high-level that makes it easier for a user to come up with a good initial data description A DB design in terms of semantic data model is not supported by the DBS and must be translated into a DB design in term of data model the DBS support. 2008 -2009 Data. Base H. TOUT 13

Our approach to create a DB A DB design in terms of relational data model may be implemented using any DBS that support this model: ACCESS, ORACLE, MY SQL, SQL SERVER Our semantic data model Our data model 2008 -2009 Data. Base H. TOUT 14

Levels of abstraction in a DBS Documents Group of users I (GU 1) Interview Group of users II(GU 2) Part of. . . the DB the GU 1 may access Modeling External Schema for GU 1 External Schema for GU 2 . . . 2008 -2009 Conceptual Schema Structure of the whole DB Data. Base Physical schema + storage details H. TOUT DB 15

DBS languages Data Description Language (DDL): to define the structure of the DB. n Data Manipulation Language (DML): to manipulate data (insert, delete, update data). n Query Language (QL): to query the DB. n The SQL Standard Language we will learn in this course represents a combination of DDL, DML and QL. n 2008 -2009 Data. Base H. TOUT 16

Chapter II THE ENTITY/RELATIONSHIP MODEL 2008 -2009 Data. Base H. TOUT 17

Introduction n The ER Model allow us to describe the data involved in a real-world enterprise in terms of : Objects and n Their relationships. n 2008 -2009 Data. Base H. TOUT 18

The ER Model Concepts Entities, attributes and entity sets n n n Entity: object in the real world that we can distinguish from other objects. Examples: me, the teacher’s table in the room 31. Attribute: an entity is described using a set of attributes. For example, the entity “Database course” is described by its cid (info 300), title (Database), number of credits (5). Entity set: collection of similar entities (entities having the same attributes. For example, each course has a cid, title and number of credits. So we may define an entity set ‘Courses’ that contains all the courses. 2008 -2009 Data. Base H. TOUT 19

Domain of values Each attribute is associated with a domain of possible values. This is called (domain constraint). n Examples: the cid is a text of 7 characters; the number of credits is an integer >1 and <7 n 2008 -2009 Data. Base H. TOUT 20

The ER Model Concepts Key and primary key n Each entity set has at least one key: minimal set of attributes whose values uniquely identify an entity in the set. n n n Key is obligatory: each entity in the set has a value of the key; Key is unique: two entities in the set should not have the same value of the key. Key is minimal: if we find a key composed from N attributes for an entity set E; any composition of N+1 attributes of E can’t be a key. There could be more than one candidate key for an entity set; for example: cid and cname are two candidate keys of the entity set courses. If so, we designate one of the candidate as the primary key 2008 -2009 Data. Base H. TOUT 21

Entity set representation Attributes Primary key(underlined) Name of the entity set 2008 -2009 Data. Base H. TOUT 22

Relationships and relationship sets n A relationship is an association among two or more entities. 2008 -2009 Data. Base H. TOUT 23

Relationships and relationship sets n A relationship set is a collection of similar relationships teaching 2008 -2009 Data. Base H. TOUT 24

Relationship set representation 2008 -2009 Data. Base H. TOUT 25

Relationship set representation n As for entities, a relationship must be uniquely identified. Normally, a relationship is identified by the participating entities without reference to the descriptive attributes. Example: a teaches association is identified by the combination (cid, tid). There are no more than one ‘teaches’ association between a given teacher and a given course. 2008 -2009 Data. Base H. TOUT 26

Relationship set representation n Several relationship sets might involve the same entity sets (1, N) (0, N) (1, 1) The cardinality ratio specifies the number of relationship instances that an entity can participate in. 2008 -2009 Data. Base H. TOUT 27

Relationship set representation n Several relationship sets might involve the same entity sets (1, N) taught by teaches (0, N) (1, 1) Roles may be added to help understanding the sense of the relationship 2008 -2009 Data. Base H. TOUT 28

Another example (1, N) (1, 1) (0, 1) 2008 -2009 Data. Base H. TOUT 29

Another example: history ? Suppose we want to store the history of departments management (1, N) (1, 1) (0, N) (0, 1) 2008 -2009 Data. Base H. TOUT 30

(1, N) (0, N) (1, N) 2000 -2008 2005 -2007 2000 -2004 1997 -2000 2004 -2005 2007 -2008 Problem: what identifies a manages instance? 2008 -2009 Data. Base H. TOUT 31

(1, N) (0, N) (1, N) 2000 -2008 2005 -2007 2000 -2004 1997 -2000 • Mona H. • Math. S 2 2004 -2005 2007 -2008 Adding From to the identifier of manages; The new identifier: (SSN, did, From) 2008 -2009 Data. Base H. TOUT 32

Ternary relationships n n Suppose now that each department has offices in several locations We want to record the locations where each employee works. 2008 -2009 Data. Base H. TOUT 33

Ternary relationships (1, N) 2008 -2009 (1, N) Data. Base H. TOUT 34

Recursive relationships n n Relationship that involves entities in the same entity set. Example: an employee Reports-To another employee; the Reports-To relationship is recursive We have to show at minimum 1 role to allow readers to understand a recursive relationship Supervised by (0, 1) Reports-To identifier : (SSN, SSN) Supervisor 2008 -2009 Supervises (0, N) Subordinate Data. Base H. TOUT 35

Weak entities The assumption (attributes associated with an entity set include a key) does not always hold. n Example: a department contains several buildings. Each contains offices; each has an office-number. Suppose that an officenumber value may be given to offices in distinct building n 2008 -2009 Data. Base H. TOUT 36

A solution should be adding an Office identifier (Oid) Not unique, not key (1, N) Another solution should be defining Offices as a weak entity: can be identified Inappropriate : we add an of attribute that does not exist really. uniquely by considering some its attributes in conjunction with the Primary key of another entity (identifying owner). (1, N) (1, 1) (1, N) An office is then identified be the pair (officenumber, bnumber) 2008 -2009 Data. Base H. TOUT 37

Inheritance: Specialization and Generalization n n Specialization is the process of defining a set of subclasses of an entity type; this entity type is called the superclass of the specialization. The set of subclasses that form a specialization is defined on the basis of some distinguishing characteristic of the entities in the superclass. For example, the set of subclasses {SECRETARY, ENGINEER, TECHNICIAN} is a specialization of the superclass EMPLOYEE that distinguishes among EMPLOYEE entities based on the job type of each entity. We may have several specializations of the same entity type based on different distinguishing characteristics. For example, another specialization of the EMPLOYEE entity type may yield the set of subclasses {SALARIED_EMPLOYEE, HOURLY_EMPLOYEE}; this specialization distinguishes among employees based on the method of pay. 2008 -2009 Data. Base H. TOUT 38

Inheritance: Specialization and Generalization n We can think of a reverse process of abstraction in which n n we suppress the differences among several entity types, identify their common features, and generalize them into a single superclass of which the original entity types are special subclasses. For example, the entity types CAR and TRUCK can be generalized into the entity type VEHICLE. Both CAR and TRUCK are now subclasses of the generalized superclass VEHICLE 2008 -2009 Data. Base H. TOUT 39

Inheritance representation local attributes 2008 -2009 Data. Base H. TOUT 40

Inheritance representation local attributes 2008 -2009 Data. Base H. TOUT 41