International Computer Institute Izmir Turkey Lecture1 Asst Prof

International Computer Institute, Izmir, Turkey Lecture-1 Asst. Prof. Dr. İlker Kocabaş UTI 510 at http: //ube. ege. edu. tr/~ikocabas/teaching/uti 510/index. html

Course Objectives 1. To understand the fundamentals of database systems n Data models n Database design n Normalisation 2. To understand the languages and facilities provided by database systems n Query languages including SQL n Integrity and security n Transactions UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 2 of 67 Modifications & additions by Cengiz Güngör

With first homework n Form teams of 2 -3 students n Plan the project with your team § Review the course material § Work on the similar problems § Discuss your project topic • Database / Table Models / Applicaton UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 3 of 67 Modifications & additions by Cengiz Güngör

Sources of Help n Principle: you are responsible for your own learning; the staff are just there to facilitate 1. Your team is your study group n Help each other, except homeworks. n But be sure that each team-member understands the material! n Explaining a concept or technique is a good way to cement it 2. Read the textbook, review the lecture slides 3. See one of the tutors during their office hours n Details on the course homepage 4. If none of the above work, please post a message to : § İlker Kocabaş ilker. kocabas@ege. edu. tr § Sercan Demirci sercan. demirci@ege. edu. tr UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 4 of 67 Modifications & additions by Cengiz Güngör

Today: Introduction, and the Entity-Relationship Model n Introduction § What is a database management system? § Why study databases? Why not use file systems? § The three-level architecture § Schemas and instances n Overview § Data models, E-R model, Relational model § Data Definition Language, Data Manipulation Language § SQL § Transaction Management, Storage Management § User types, database administrator § System Structure n Entity-Relationship Model UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 5 of 67 Modifications & additions by Cengiz Güngör

Introduction

Practice and Theory n Practice n Theory § Tables, columns, rows, § Relational model: keys relations, attributes, tuples § SQL § Relational algebra, § Application structure equivalences § Logical & physical § Functional database design dependencies, normalization § Transactions § Security § Schedules, serializability UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 7 of 67 Modifications & additions by Cengiz Güngör

What is a Database Management System? n Collection of data § Interrelated data § Relevant to some endeavour n Software to access the data § Convenient § Efficient n History § 1950 s-60 s: magnetic tape and punched cards § 1960 s-70 s: hard disks, random access, file systems § 1970 s-80 s: relational model becoming competitive § 1980 s-90 s: relational model dominant, object-oriented databases § 1990 s-00 s: web databases and XML UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 8 of 67 Modifications & additions by Cengiz Güngör

Why study databases? n They touch every aspect of our lives n Applications: § Banking: all transactions § Airlines: reservations, schedules § Universities: registration, course enrolment, grades § Sales: customers, products, purchases § Manufacturing: production, inventory, orders, supply chain § Human resources: employee records, salaries, tax deductions § Telecommunications: subscribers, usage, routing § Computer accounts: privileges, quotas, usage § Records: climate, stock market, library holdings n Explosion of unstructured data on the web: § Large document collections § Image databases, streaming media UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 9 of 67 Modifications & additions by Cengiz Güngör

Why not use file systems? n Data redundancy and inconsistency § Multiple file formats § Duplication of information in different files n Difficulty in accessing data § Need to write a new program to carry out each new task n Data isolation § Multiple files and formats n Integrity problems § Integrity constraints (e. g. account balance > 0) become part of program code § Hard to add new constraints or change existing ones UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 10 of 67 Modifications & additions by Cengiz Güngör

Why not use file systems? (cont) n Maintenance problems § When we add a new field, all existing applications must be modified to ignore it n Atomicity of updates § Failures may leave database in an inconsistent state with partial updates carried out § E. g. transfer of funds from one account to another should either complete or not happen at all n Concurrent access by multiple users § Concurrent accessed needed for performance § Uncontrolled concurrent accesses can lead to inconsistencies • E. g. two people reading a balance and updating it at the same time § Security problems n Database systems offer solutions to all the above problems UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 11 of 67 Modifications & additions by Cengiz Güngör

The three-level architecture n Physical level: how a record is stored on disk n Logical level: describes data stored in database, and the relationships among the data. type customer = record name : string; street : string; city : integer; end; n View level: application-specific selections and arrangements of the data § hide details of data types § Views can also hide information for security reasons UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 12 of 67 Modifications & additions by Cengiz Güngör

The three-level architecture (cont) An architecture for a database system UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 13 of 67 Modifications & additions by Cengiz Güngör

Schemas vs Instances n Schema § the logical structure of the database § e. g. , the database consists of information about a set of customers and accounts and the relationship between them § Analogous to type information of a variable in a program n Instance § the actual content of the database at a particular point in time § Analogous to the value of a variable n Physical Data Independence § the ability to modify the physical schema without changing the logical schema § Applications depend on the logical schema § Database engines take care of efficient storage and query processing UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 14 of 67 Modifications & additions by Cengiz Güngör

Overview

Data Models n A collection of tools for describing § data relationships § data semantics § data constraints n Entity-Relationship model n Relational model n Other models: § object-oriented model § semi-structured data models (XML) § Older models: network model and hierarchical model UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 16 of 67 Modifications & additions by Cengiz Güngör

Entity-Relationship Model Example of schema in the entity-relationship model UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 17 of 67 Modifications & additions by Cengiz Güngör

Relational Model Attributes n Example of tabular data in the relational model Customer-id customername 192 -83 -7465 Johnson 019 -28 -3746 Smith 192 -83 -7465 Johnson 321 -12 -3123 Jones 019 -28 -3746 Smith customerstreet customercity accountnumber Alma Palo Alto A-101 North Rye A-215 Alma Palo Alto A-201 Main Harrison A-217 North Rye A-201 UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 18 of 67 Modifications & additions by Cengiz Güngör

A Sample Relational Database UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 19 of 67 Modifications & additions by Cengiz Güngör

Data Definition Language (DDL) n Specification notation for defining the database schema § E. g. create table account ( account-number char(10), balance integer) n DDL compiler generates a set of tables stored in a data dictionary: § Database schema § Specification of storage structures and access methods UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 20 of 67 Modifications & additions by Cengiz Güngör

Data Manipulation Language (DML) n Language for accessing and manipulating the data organized by the appropriate data model § DML also known as query language n Two classes of languages § Procedural – user specifies what data is required and how to get those data § Nonprocedural – user specifies what data is required without specifying how to get those data n SQL is the most widely used query language UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 21 of 67 Modifications & additions by Cengiz Güngör

SQL n SQL: widely used non-procedural language § E. g. find the name of the customer with customer-id 192 -83 -7465 select customer-name from customer where customer-id = ‘ 192 -83 -7465’ § E. g. find the balances of all accounts held by the customer with customer-id 192 -83 -7465 select account. balance from depositor, account where depositor. customer-id = ‘ 192 -83 -7465’ and depositor. account-number = accountnumber n Application programs generally access databases through: § Language extensions to allow embedded SQL (e. g. PHP) § Application program interface (e. g. ODBC) which allow SQL queries to be sent to a database UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 22 of 67 Modifications & additions by Cengiz Güngör

Database Users n Users are differentiated by the way they expect to interact with the system n Application programmers – interact with system through DML calls n Sophisticated users – form requests in a database query language n Specialized users – write specialized database applications that do not fit into the traditional data processing framework n Naïve users – invoke one of the permanent application programs that have been written previously § E. g. people accessing database over the web, bank tellers, clerical staff UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 23 of 67 Modifications & additions by Cengiz Güngör

Database Administrator n Coordinates all the activities of the database system; the database administrator has a good understanding of the enterprise’s information resources and needs. n Database administrator's duties include: § Schema definition § Storage structure and access method definition § Schema and physical organization modification § Granting user authority to access the database § Specifying integrity constraints § Acting as liaison with users § Monitoring performance and responding to changes in requirements UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 24 of 67 Modifications & additions by Cengiz Güngör

Transaction Management n A transaction is a collection of operations that performs a single logical function in a database application § E. g. transfer funds from one account to another n Transaction-management component ensures that the database remains in a consistent state despite system failures n Concurrency-control manager controls the interaction among the concurrent transactions, to ensure the consistency of the database. § E. g. simultaneous withdrawals UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 25 of 67 Modifications & additions by Cengiz Güngör

Storage Management n Storage manager is a program module that provides the interface between the low-level data stored in the database and the application programs and queries submitted to the system. n The storage manager is responsible to the following tasks: § interaction with the file manager § efficient storing, retrieving and updating of data UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 26 of 67 Modifications & additions by Cengiz Güngör

Overall System Structure UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 27 of 67 Modifications & additions by Cengiz Güngör

Application Architectures § Two-level architecture: E. g. client programs using ODBC/JDBC to communicate with a database § Three-level architecture: E. g. web-based applications, and applications built using “middleware” § Your projects will use three-tier architecture UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 28 of 67 Modifications & additions by Cengiz Güngör

Entity-Relationship Model

Entity-Relationship Model n Entity Sets n Relationship Sets n Design Issues n Mapping Constraints n Keys n E-R Diagram n Extended E-R Features n Design of an E-R Database Schema n Reduction of an E-R Schema to Tables UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 30 of 67 Modifications & additions by Cengiz Güngör

Entity Sets n A database can be modeled as: § a collection of entities, § relationship among entities. n An entity is an object that exists and is distinguishable from other objects. § E. g. specific person, company, event, plant n Entities have attributes § E. g: people have names and addresses n An entity set is a set of entities of the same type that share the same properties. § Example: set of all persons, companies, courses, books UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 31 of 67 Modifications & additions by Cengiz Güngör

Entity Sets customer and loan customer-id customer- customername street city UBI 502 Database Management Systems loan- amount number ©Silberschatz, Korth and Sudarshan 1. 32 of 67 Modifications & additions by Cengiz Güngör

Attributes n An entity is represented by a set of attributes, that is descriptive properties possessed by all members of an entity set. Example: customer = (customer-id, customer-name, customer-street, customer-city) loan = (loan-number, amount) n Domain – the set of permitted values for each attribute n Attribute types: § Simple and composite attributes. § Single-valued and multi-valued attributes • E. g. multivalued attribute: phone-numbers § Derived attributes • Can be computed from other attributes • E. g. age, given date of birth UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 33 of 67 Modifications & additions by Cengiz Güngör

Composite Attributes UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 34 of 67 Modifications & additions by Cengiz Güngör

Relationship Sets n A relationship is an association among several entities Example: Hayes customer entity depositor relationship set A-102 account entity n A relationship set is a relation over n 2 entity sets Ei : {(e 1, e 2, … en) | ei Ei} Example: (Hayes, A-102) depositor UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 35 of 67 Modifications & additions by Cengiz Güngör

Relationship Set borrower UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 36 of 67 Modifications & additions by Cengiz Güngör

Relationship Sets (Cont. ) n An attribute can also be property of a relationship set. n For instance, the depositor relationship set between entity sets customer and account may have the attribute access-date UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 37 of 67 Modifications & additions by Cengiz Güngör

Degree of a Relationship Set n The number of entity sets that participate in a relationship set n Relationship sets that involve two entity sets are binary (or degree two). Generally, most relationship sets in a database system are binary. n Relationship sets may involve more than two entity sets. HE. g. Suppose employees of a bank may have jobs (responsibilities) at multiple branches, with different jobs at different branches. Then there is a ternary relationship set between entity sets employee, job and branch n Relationships between more than two entity sets are rare. Most relationships are binary. (More on this later. ) UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 38 of 67 Modifications & additions by Cengiz Güngör

Mapping Cardinalities n Express the number of entities to which another entity can be associated via a relationship set. n Most useful in describing binary relationship sets. n For a binary relationship set the mapping cardinality must be one of the following types: § One to one § One to many § Many to one § Many to many UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 39 of 67 Modifications & additions by Cengiz Güngör

Mapping Cardinalities One to one One to many Note: Some elements in A and B may not be mapped to any elements in the other set UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 40 of 67 Modifications & additions by Cengiz Güngör

Mapping Cardinalities Many to one Many to many Note: Some elements in A and B may not be mapped to any elements in the other set UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 41 of 67 Modifications & additions by Cengiz Güngör

Mapping Cardinalities affect ER Design n Can make access-date an attribute of account, instead of a relationship attribute, if each account can have only one customer n I. e. , the relationship from account to customer is many to one, or equivalently, customer to account is one to many UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 42 of 67 Modifications & additions by Cengiz Güngör

E-R Diagrams n Rectangles represent entity sets. n Diamonds represent relationship sets. n Lines link attributes to entity sets and entity sets to relationship sets. n Ellipses represent attributes n Double ellipses represent multivalued attributes. n Dashed ellipses denote derived attributes. n Underline indicates primary key attributes (will study later) UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 43 of 67 Modifications & additions by Cengiz Güngör

E-R Diagram With Composite, Multivalued, and Derived Attributes UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 44 of 67 Modifications & additions by Cengiz Güngör

Relationship Sets with Attributes UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 45 of 67 Modifications & additions by Cengiz Güngör

Roles n Entity sets of a relationship need not be distinct n The labels “manager” and “worker” are called roles; they specify how employee entities interact via the works-for relationship set. n Roles are indicated in E-R diagrams by labelling the lines that connect diamonds to rectangles. n Role labels are optional, and are used to clarify semantics of the relationship UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 46 of 67 Modifications & additions by Cengiz Güngör

Cardinality Constraints n 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. n 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 UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 47 of 67 Modifications & additions by Cengiz Güngör

One-To-Many Relationship n 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 UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 48 of 67 Modifications & additions by Cengiz Güngör

Many-To-One Relationships n 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 UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 49 of 67 Modifications & additions by Cengiz Güngör

Many-To-Many Relationship n A customer is associated with several (possibly 0) loans via borrower n A loan is associated with several (possibly 0) customers via borrower UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 50 of 67 Modifications & additions by Cengiz Güngör

Participation of an Entity Set in a Relationship Set n Total participation (indicated by double line): every entity in the entity set participates in at least one relationship in the relationship set n E. g. participation of loan in borrower is total n every loan must have a customer associated to it via borrower n Partial participation: some entities may not participate in any relationship in the relationship set n E. g. participation of customer in borrower is partial UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 51 of 67 Modifications & additions by Cengiz Güngör

Alternative Notation for Cardinality Limits n Cardinality limits can also express participation constraints UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 52 of 67 Modifications & additions by Cengiz Güngör

Keys n A super key of an entity set is a set of one or more attributes whose values uniquely determine each entity. n A candidate key of an entity set is a minimal super key § Customer-id is candidate key of customer § account-number is candidate key of account n Although several candidate keys may exist, one of the candidate keys is selected to be the primary key. UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 53 of 67 Modifications & additions by Cengiz Güngör

Keys for Relationship Sets n The combination of primary keys of the participating entity sets forms a super key of a relationship set. § (customer-id, account-number) is the super key of depositor § NOTE: this means a pair of entity sets can have at most one relationship in a particular relationship set. • E. g. if we wish to track all access-dates to each account by each customer, we cannot assume a relationship for each access. We can use a multivalued attribute though n Must consider the mapping cardinality of the relationship set when deciding the what are the candidate keys n Need to consider semantics of relationship set in selecting the primary key in case of more than one candidate key UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 54 of 67 Modifications & additions by Cengiz Güngör

UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 55 of 67 Modifications & additions by Cengiz Güngör

UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 56 of 67 Modifications & additions by Cengiz Güngör

UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 57 of 67 Modifications & additions by Cengiz Güngör

Not NULL UBI 502 Database Management Systems Non-negative ©Silberschatz, Korth and Sudarshan 1. 58 of 67 Modifications & additions by Cengiz Güngör

E-R Diagram with a Ternary Relationship UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 59 of 67 Modifications & additions by Cengiz Güngör

Cardinality Constraints on Ternary Relationship n We allow at most one arrow out of a ternary (or greater degree) relationship to indicate a cardinality constraint n E. g. an arrow from works-on to job indicates each employee works on at most one job at any branch. n If there is more than one arrow, there are two ways of defining the meaning. § E. g a ternary relationship R between A, B and C with arrows to B and C could mean § 1. each A entity is associated with a unique entity from B and C or § 2. each pair of entities from (A, B) is associated with a unique C entity, and each pair (A, C) is associated with a unique B § Each alternative has been used in different formalisms § To avoid confusion we outlaw more than one arrow UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 60 of 67 Modifications & additions by Cengiz Güngör

Binary vs Non-Binary Relationships n Some relationships that appear to be non-binary may be better represented using binary relationships § E. g. A ternary relationship parents, relating a child to his/her father and mother, is best replaced by two binary relationships, father and mother • Using two binary relationships allows partial information (e. g. only mother being know) § But there are some relationships that are naturally non-binary • E. g. works-on UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 61 of 67 Modifications & additions by Cengiz Güngör

Converting Non-Binary Relationships to Binary Form n In general, any non-binary relationship can be represented using binary relationships by creating an artificial entity set. § Replace R between entity sets A, B and C by an entity set E, and three relationship sets: 1. RA, relating E and A 3. RC, relating E and C 2. RB, relating E and B § Create a special identifying attribute for E § Add any attributes of R to E § For each relationship (ai , bi , ci) in R, create 1. a new entity ei in the entity set E 2. add (ei , ai ) to RA 3. add (ei , bi ) to RB 4. add (ei , ci ) to RC UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 62 of 67 Modifications & additions by Cengiz Güngör

Converting Non-Binary Relationships (Cont. ) n Also need to translate constraints § Translating all constraints may not be possible § There may be instances in the translated schema that cannot correspond to any instance of R • Exercise: add constraints to the relationships RA, RB and RC to ensure that a newly created entity corresponds to exactly one entity in each of entity sets A, B and C § We can avoid creating an identifying attribute by making E a weak entity set (described shortly) identified by the three relationship sets UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 63 of 67 Modifications & additions by Cengiz Güngör

Design Issues n Use of entity sets vs. attributes Choice mainly depends on the structure of the enterprise being modeled, and on the semantics associated with the attribute in question. n Use of entity sets vs. relationship sets Possible guideline is to designate a relationship set to describe an action that occurs between entities n Binary versus n-ary relationship sets Although it is possible to replace any nonbinary (n-ary, for n > 2) relationship set by a number of distinct binary relationship sets, a nary relationship set shows more clearly that several entities participate in a single relationship. n Placement of relationship attributes UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 64 of 67 Modifications & additions by Cengiz Güngör

Summary of Symbols Used in E-R Notation UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 65 of 67 Modifications & additions by Cengiz Güngör

Summary of Symbols (Cont. ) UBI 502 Database Management Systems ©Silberschatz, Korth and Sudarshan 1. 66 of 67 Modifications & additions by Cengiz Güngör