Database Sys 2.pdf

Chapter 2 Database System Concepts and Architecture Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Data Models Data Model: Collection of concepts that describe t...

Chapter 2 Database System Concepts and Architecture Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Data Models Data Model: Collection of concepts that describe the structure of database – A set of concepts to describe the structure of a database, the operations for manipulating the data, and the constraints that the data should follow. Data Model Structure and Constraints: – Data Model constructs define the database structure – Data model constructs often include: data elements and their data types (often called attributes); grouping of related elements into entities (also called objects or records or tuples); and relationships among entities – Constraints specify restrictions on the stored data; the data that satisfies the constraints is called valid data Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Data Model: Example 1. Data Model Structure Entities: group of related elements Students Courses Instructors Loading… Enrollments Grades Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Data Model: Example Students and Enrollments (One-to-Many) Relationship: One student can be enrolled in multiple courses, but each enrollment record is tied to one specific student. Courses and Enrollments (One-to-Many) Relationship: One course can have multiple students enrolled, but each enrollment record is tied to one specific course. Students and Grades (One-to-Many) Relationship: One student can have multiple grades (one for each course), but each grade record is tied to one specific student. Courses and Instructors (Many-to-One) Relationship: Many courses can be taught by one instructor, but each course is taught by only one instructor. Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Data Model: Example Student Operations Register Student: Insert a new student record into the Students table. Update Student Info: Modify student details (e.g., address, email, phone). View Student Profile: Retrieve details of a student by student_id. Delete Student: Remove a student from the system. Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Data Model: Example Primary Key Constraints: Each table has a primary key (student_id) to uniquely identify each record. Unique Constraint: Ensure that Students.email is unique to avoid duplicates. Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Categories of Data Models & Conceptual (high-level, semantic) data models: - – Provide concepts that are close to the way many users perceive data. in noall (Also called entity-based or object-based data models.) Implementation (representational) data models: by end-users way concepts data , is but that organized may be in easily also not too far from computer understood the storage – Provide concepts used by many commercial DBMS implementations (e.g. relational data models used in many commercial systems). Physical (low-level, internal) data models: for computer Specialists , not normal end-users – Provide concepts that describe details of how data is stored in the computer. These are usually specified in an ad-hoc manner through DBMS design and administration manuals Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Categories of Data Models Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Database Schema versus Database State Database Schema: drawing No Description only... – The description of a database. – Includes descriptions of the database structure, relationships, data types, and constraints Schema Diagram: Loading… Drawing – An illustrative display of (some aspects of) a database schema Schema Construct: – A component of the schema or an object within the schema, e.g., STUDENT, COURSE, Name Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Example of a Database Schema > - Schema Construct Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Database Schema vs. Database State (cont.) Database State: = – The actual data stored in a database at a particular moment in time. This includes the collection of all the data in the database. – Also called a database instance (or occurrence or snapshot). NOTE: The term instance is also used to refer to individual database components, e.g. a record instance, table instance, or entity instance Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Database Schema vs. Database State Database State: – Refers to the content of a database at a particular moment in time. Initial Database State: – Refers to the database state when it is initially loaded into the system. Valid State: – A state that satisfies the structure and constraints of the database. Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Database Schema vs. Database State (cont.) Distinction Does not change A – The database schema changes very - infrequently. may change over time & – The database state changes every time the - database is updated. Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Three-Schema Architecture (cont.) Defines DBMS schemas at three levels: – Internal schema at the internal level to describe physical storage structures and access paths (e.g indexes). Deals with cletails Typically uses a physical data model. – Nodetails Conceptual schema at the conceptual level to describe the of physa > - describes entities , data types relation ships, , structure and constraints for the whole database for a user operations I n constraints. community of users. Uses an implementation (or a conceptual) data model. – Users * sig- 1 11 database I External schemas at the external level to describe the various user views. b Usually uses the same data model as the conceptual schema. Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley The three-schema architecture (cont.) Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Three-Schema Architecture (cont.) Mappings among schema levels are needed to transform requests and data. – Users and programs refer to an external schema, and are mapped by the DBMS to the internal schema for execution. – Data extracted from the internal DBMS level is reformatted to match the user’s external view (e.g. formatting the results of an SQL query for display as a Web page) Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  DBMS Interfaces tools Applications that allow user Stand-alone query language interfaces to interact with DBMS a using a query language (typically SQL) – Example: Typing SQL queries directly through the DBMS interactive SQL interface (e.g. SQL*Plus in ORACLE) Programmer interfaces for embedding DML in programming languages · Allow users from their to interact code. with databases directly User-friendly interfaces (often Web-based) – Menu-based, forms-based, graphics-based, etc. Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  DBMS Programming Language Interfaces Programmer interfaces for embedding DML in a programming language (see Chapter 13): – Embedded Approach: e.g embedded SQL (for C, C++, etc.), SQLJ (for Java) – Procedure Call Approach: e.g. JDBC for Java, ODBC for other programming languages – Database Programming Language Approach: e.g. ORACLE has PL/SQL, a programming language based on SQL; language incorporates SQL and its data types as integral components Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  User-Friendly and Web-based DBMS Interfaces present list of options or commands organized in menus. & dropdown for categories products services contact with menus like ex : A website , , us... etc. – Menu-based, popular for browsing on the web – Forms-based, designed for naïve users is => Ex : online registration – Graphics-based => Graphical elements like icons buttons , , ' images (Point and Click, Drag and Drop, etc.) – Natural language: requests in written English – Combinations of the above: For example, both menus and forms used extensively in Web database interfaces Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Other DBMS Interfaces – Speech as Input and Output – Web Browser as an interface – Parametric interfaces, e.g., bank tellers using function keys. => Ex : Users Search results can adjust or setting data or filters retrieval. to refine their – Interfaces for the DBA: Creating user accounts, granting authorizations Setting system parameters Changing schemas or storage structures/access paths (physical database) Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Other Tools (cont.) Application Development Environments and CASE (computer-aided software engineering) tools often have a database design component Examples: – PowerBuilder (Sybase) – JBuilder (Borland) – JDeveloper 10G (Oracle) Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Database System - DBMS Architectures (cont.) Client : Server : Application program Database its t user interface DBMS i Basic 2-tier Client-Server Architecture: = Specialized Server nodes with Specialized functions – Print server - gyS : igned - – File server - – DBMS server – Web server – Email server Client nodes can access the specialized servers as needed Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Logical two-tier client server architecture Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Two Tier Client-Server DBMS Architecture A program running at a client may connect to several DBMSs (also called data sources). In general, data sources can be files or other non- DBMS software that manages data. Loading… Client focuses on user interface interactions and only accesses database when needed. In some cases (e.g. some object DBMSs), more functionality is transferred to clients (e.g. data dictionary functions, optimization and recovery across multiple servers, etc.) Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Three Tier Client-Server DBMS 3 levs Architecture Client : User Interfere Acceptrequest Application Application Program : web server Businesslogic layer processesrequentSadasses proceeda the to database Server Common for Web applications Between Client is Sever Server : Database DBMS t Third intermediate layer (middle tier) called Application Server or Web Server: Procedures / constraints – Stores the web connectivity software and the business logic - part of the application – Accesses and updates data on 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 Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Three-tier client-server architecture Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Client nodes Provide appropriate interfaces through a client software module to access and utilize the various server resources. Clients may be PCs or Workstations (or even diskless machines) with the client software installed. Connected to the servers via a network. – LAN: local area network – wireless network – etc. Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  DBMS Server 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 Client and server must install appropriate client module and server module software for ODBC or JDBC See Chapter 13 Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Chapter 3 The Basic (Flat) Relational Model Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Chapter 3 Outline The Relational Data Model and Relational Database Constraints Relational Model Constraints and Relational Database Schemas Update Operations, Transactions, and Dealing with Constraint Violations Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Pre-Relational model Hierarchical Model Organizes data in a tree-like structure, where records (or nodes) have a parent-child relationship. Each parent can have multiple Loading… children, but each child has only one parent, creating a rigid Are folders on a laptop relational or hierarchy. hierarchical? The data relationships are modeled using pointers. Folders on a laptop are structured in a hierarchical model rather than a relational model. Here's why: Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Pre-Relational model Network Model The Network Model extends the hierarchical model by allowing many-to-many relationships. Data is organized in a graph-like structure where records can Are flights in an airline system relational have multiple parents and or network? multiple children. In an airline system, many flights Relationships between records can serve many airports, and are maintained via sets and many airports can handle many pointers. flights. This creates a network of relationships between flights and airports. Copyright © 2011 Ramez Elmasri and Shamkant Navathe  The Relational Data Model and Relational Database Constraints Relational model First commercial implementations available in early 1980s commercial system Loading… Has been implemented in a large number of Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Relational Model Concepts Represents data as a collection of relations Table of values Table name (Relation Name) Column names (attribute) Interpret the meaning of the values in each row Row (Tuple) Represents a collection of related data values Fact that typically corresponds to a real-world entity or relationship Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Relational Model Concepts (cont’d.) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Domains, Attributes, Tuples, and Relations Domain D - Set of atomic values an attributes can have Atomic: Each value indivisible Each attribute in a table is associated with a specific domain that defines its permissible data types or range of values. If you have a column for "Age," the domain could be integers ranging from 0 to 120. For a "Gender" column, the domain might be restricted to values like "Male" and "Female." Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Domains, Attributes, Tuples, and Relations (cont’d.) Relation schema R Denoted by R(A1, A2,...,An) Made up of a relation name R and a list of attributes, A1, A2,..., An Attribute Ai Name of a role played by some domain D in the relation schema R Example: Customers(CustomerID, Name, Email, PhoneNumber) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Domains, Attributes, Tuples, and # Relations (cont’d.) of Attributes that have M a scheme will Degree (or arity) of a relation Number of attributes n of its relation schema What is the degree of the following relation schema? Customers(CustomerID, Name, Email, PhoneNumber) Degree => = 4 Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Characteristics of Relations Tuples are unique => if I is different to not unique No two rows have exactly the same values for all attributes Loading… Ordering of tuples in a relation Relation defined as a set of tuples Elements have no order among them order doesn't matter bez we are talking about Sets. Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Characteristics of Relations Ordering of values within a tuple and an alternative definition of a relation Order of attributes and values is not that important As long as correspondence between attributes and values maintained (StudentID: 001, Name: Alice, Age: 20) 3 order doesn't matter as order doesn't long as we specily (Name: Alice, StudentID: 001, Age: 20) matter the attribute name. => name not specified Can't change : order Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Characteristics of Relations (cont’d.) for the Son for barbara. do we care: ? - if we example want to update God is It is if should specify 2nna isimm to this is incase it not we No , , 6... &I b gji Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Characteristics of Relations (cont’d.) Use the first definition of relation Attributes and the values within tuples are ordered Students(StudentID, Name, Age) (001, Alice, 20) Simpler notation (002, Bob, 22) Alternative definition of a relation Tuple considered as a set of (, ) pairs Each pair gives the value of the mapping from an attribute Ai to a value vi from dom(Ai) (StudentID: 001, Name: Alice, Age: 20) (Name: Bob, StudentID: 002, Age: 22) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Characteristics of Relations& (cont’d.) Values in tuples Each value in a tuple is atomic Flat relational model Composite and multivalued attributes not allowed First normal form assumption Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Characteristics of Relations (cont’d.) Multivalued attributes Must be represented by separate relations Let's say we have a Students relation where a student can enroll in multiple courses. The Courses attribute is multivalued. Can we do the below? No Students(StudentID, Name, Courses) (001, Alice, Math, Physics) (002, Bob, Chemistry) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Characteristics of Relations (cont’d.) Multivalued attributes Must be represented by separate relations Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Characteristics of Relations (cont’d.) Composite attributes Represented only by simple component attributes in basic relational model Consider an attribute like Address, which is a composite attribute made up of Street, City, State, and ZipCode. Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Characteristics of Relations (cont’d.) NULL values Represent the values of attributes that may be unknown or may not apply to a tuple Meanings for NULL values Value unknown Value exists but is not available Attribute does not apply to this tuple (also known as value undefined) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Relational Model Notation Name of a relation schema: STUDENT Indicates the current set of tuples in that relation Notation: STUDENT(Name, Ssn,...) Refers only to relation schema Attribute A can be qualified with the relation name R to which it belongs Using the dot notation R.A Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Relational Model Constraints Constraints Restrictions on the actual values in a database state => Ex.. · Age Name can't can't bere be numbers Derived from the rules in the miniworld that the database represents Inherent model-based constraints or implicit constraints Inherent in the data model Such as age needs to be positive, ID number must exist Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Relational Model Constraints (cont’d.) Schema-based constraints or explicit constraints Can be directly expressed in schemas of the data model Examples: age >18, email is unique, primary key defined * Ex. of explicit constraint : ID being unique Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Relational Model Constraints (cont’d.) Application-based or semantic constraints or business rules Cannot be directly expressed in schemas Expressed and enforced by application program Example: password length, account balance before doing a transaction Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Domain Constraints Typically include: Numeric data types for integers and real numbers Characters Booleans Fixed-length strings => Ex : Dates : DD/MMYEAR Variable-length strings Date, time, timestamp Money Other special data types Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Key Constraints and Constraints on NULL Values If we haveI exactly the same that No two tuples can have the same are : - redundant Information combination of values for all their attributes. Superkey => No 2 tuples & Sets have theSame data Superkey. 11 A superkey is any set of one or more attributes that can uniquely identify a tuple in a relation A superkey may contain extra, unnecessary attributes that are not needed to uniquely identify the tuple No two distinct tuples in any state r of R can have the same value for SK Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Key Constraints and Constraints on NULL Values Consider a Students(StudentID, Name, & & Email) relation, I Possible Superkeys for this relation: {StudentID} Email alone {StudentID, Name} E {StudentID, Email} name alone are {StudentID, Name, Email} not Super keys Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Key Constraints and Constraints on NULL Values (cont’d.) Key The smallest set of attributes that can uniquely identify a tuple in a relation, with no unnecessary attributes. Superkey of R Minimal superkey Removing any attribute A from K leaves a set of attributes K that is not a superkey of R any more. Ef is every key a Superkey ? Yes , bat a Superkey can be 1 or more But not Juperkey every is a they ; because it is not only 1 , it can be more Copyright © 2011 Ramez Elmasri and Shamkant Navathe Key Constraints and Constraints on NULL Values (cont’d.) Aspect Superkey Key (Candidate Key) A set of one or more A minimal superkey (no Definition attributes that can unnecessary attributes). uniquely identify a tuple. Can have more than the Minimal (smallest set of Size necessary attributes. attributes). Ensures uniqueness, but Ensures uniqueness with Uniqueness may include extra no extra columns. columns. {StudentID, Name}, Examples {StudentID}, {StudentID, {StudentID}, {Email} Name, Email} Every key is a superkey, Relationship Every key is a superkey. but not all superkeys are keys. Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Key Constraints and Constraints on NULL Values (cont’d.) Candidate key - Set of Attributes that can be a key Relation schema may have more than one key Primary key of the relation => cannot be null Loading… Smallest in Size between other candidates Designated among candidate keys Underline attribute Other candidate keys are designated as unique keys Copyright © 2011 Ramez Elmasri and Shamkant Navathe Key Constraints and Constraints on NULL Values (cont’d.) Can't be a candidate key because there is a repetition & I Copyright © 2011 Ramez Elmasri and Shamkant Navathe for Ex Mar Isn ⑪ : Check in Sor figure 36;. Integrity, Referential Integrity, Sume Attributes hore the same ; &- Al link 9> doesn't Goo and Foreign Keys (cont’d.) have to no Foreign key rules: - > - A primary key in table a In used in another table The attributes in FK have the same domain(s) as the primary key attributes PK Value of FK in a tuple t1 of the current state r1(R1) either occurs as a value of PK for some tuple t2 in the current state r2(R2) or is NULL Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Integrity, Referential Integrity, and Foreign Keys Entity integrity constraint > - In a Sigle relation No primary key value can be NULL Referential integrity constraint Specified between two relations Maintains consistency among tuples in two relations & 2 tables can - we infert Su Chicagol ? , Yes , because we have a department with Dnumber 4. - 1 + ~ &3 chicago ? , no , because no department with Dnumber 3 exist - Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Relational Databases and Relational Database Schemas Invalid state (cont’d.) Does not obey all the integrity constraints Valid state Satisfies all the constraints in the defined set of integrity constraints IC Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Integrity, Referential Integrity, and Foreign Keys (cont’d.) Diagrammatically display referential integrity constraints Directed arc from each foreign key to the relation it references All integrity constraints should be specified on relational database schema Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe  CREATE TABLE CREATE TABLE employee ( fname varchar(15), minit varchar(1), ---- & lname varchar(15), = ssn char(9), -- I - & I bdate date, X => address varchar(50), - sex char, ---- I....... = salary decimal(10,2), -- - superssn char(9), dno number(4) ); Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Update Operations, Transactions, and Dealing with Constraint Violations Operations of the relational model can be categorized into retrievals and updates Basic operations that change the states of relations in the database: Insert Delete Update (or Modify) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  The Insert Operation new ↓ tuple in a relation Provides a list of attribute values for a new tuple t that is to be inserted into a relation R Can violate a constraint. If an insertion violates one or more constraints Default option is to reject the insertion Copyright © 2011 Ramez Elmasri and Shamkant Navathe  INSERT Operation attributes INSERT INTO employee VALUES => order matters here bez are not mentioned ('James','E','Borg','888665555','10-NOV-27','450 Stone, Houston, TX','M',55000,null,null); INSERT INTO employee VALUES ('Franklin','T','Wong','333445555','08-DEC-45','638 Voss, Houston, TX','M',40000,'888665555',null); INSERT INTO employee VALUES ('Jennifer','S','Wallace','987654321','20-JUN-31','291 Berry, Bellaire, TX','F',43000,'888665555',null); Copyright © 2011 Ramez Elmasri and Shamkant Navathe · Son could not be null INSERT Question Show if the following operations are correct or not. If not, show why: INSERT INTO employee VALUES ('James','E','Borg','888665555','10-NOV- 27','450 Stone, Houston, TX','M',55000,null,null); · Date Salary should be decimal Copyright © 2011 Ramez Elmasri and Shamkant Navathe  INSERT Question Suppose this insert operation is applied directly to the database of Figure 3.6. Discuss all integrity constraints violated by the operation, if any, and the different ways of enforcing these constraints: Check a number 5 * we can't infect that is , Primary key * Date : different format is inserted * So has to exist in primary key table /employee table) Insert < 'Production’, 5, ‘773785555', '01- OCT-88' > into DEPARTMENT. Copyright © 2011 Ramez Elmasri and Shamkant Navathe  possible Copyright © 2011 Ramez Elmasri and Shamkant Navathe  INSERT Question Answer Violates both the key constraint and referential integrity. Violates the key constraint because there already exists a DEPARTMENT tuple with DNUMBER=5. We may enforce this constraint by: (i) rejecting the insertion, or (ii) changing the value of DNUMBER in the new DEPARTMENT tuple to a value that does not violate the key constraint. Violates referential integrity because Mgr_ssn= ' 773785555 ' and there is no tuple in the EMPLOYEE relation with SSN=' 773785555 '. We may enforce the constraint by: (i) rejecting the insertion, (ii) changing the value of MGRSSN to an existing SSN value in EMPLOYEE, or (iii) inserting a new EMPLOYEE tuple with SSN='773785555 '. Copyright © 2011 Ramez Elmasri and Shamkant Navathe  The Delete Operation Can violate only referential integrity If tuple being deleted is referenced by foreign keys from other tuples Restrict => if linked to any other attribute: do not allow deleting Reject the deletion Cascade Propagate the deletion by deleting tuples that reference the tuple that is being deleted Set null or set default Modify the referencing attribute values that cause the violation Copyright © 2011 Ramez Elmasri and Shamkant Navathe  DELETE Operation DELETE FROM employee WHERE ssn = ‘888665555'; Referential Integrity Violation DELETE FROM employee WHERE ssn = ‘333445555'; RJU Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Delete Operation Suppose this delete operation is applied directly to the database of Figure 3.6. Discuss all integrity constraints violated by the operation, if any, and the different ways of enforcing these constraints: Delete the DEPARTMENT tuples with Dnumber = 1. jus * - S inned %0 -05 reject j %s James... 24-48... % 9.. Violates referential integrity because EMPLOYEE has a Dno =1. Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe  The Update Operation > Necessary to specify a condition on attributes of relation Select the tuple (or tuples) to be modified If attribute not part of a primary key nor of a foreign key Usually causes no problems Updating a primary/foreign key Similar issues as with Insert/Delete Copyright © 2011 Ramez Elmasri and Shamkant Navathe  UPDATE - Operation UPDATE - employee SET fname = ‘Smith' WHERE ssn = ‘888665555'; · M Copyright © 2011 Ramez Elmasri and Shamkant Navathe  UPDATE Question Modify the Dno attribute of the EMPLOYEE tuple with Dno = ‘5' to ‘6'. Violates referential integrity because the new value of Dno=‘6' and there is no tuple in the DEPARTMENT relation with Dno = ‘6'. We may enforce the constraint by: (i) rejecting the deletion, or (ii) inserting a new DEPARTMENT tuple with Dno = ‘6'. Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Summary Characteristics differentiate relations from ordinary tables or files Classify database constraints into: Inherent model-based constraints, explicit schema-based constraints, and application- based constraints Modification operations on the relational model: Insert, Delete, and Update Copyright © 2011 Ramez Elmasri and Shamkant Navathe  cated a Chapter 4 SQL: Data Definition, Constraints, and Basic Queries and Updates Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Chapter 4 Outline SQL Data Definition and Data Types Specifying Constraints in SQL Basic Retrieval Queries in SQL Loading… INSERT, DELETE, and UPDATE Statements in SQL Additional Features of SQL Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Basic SQL SQL language Considered one of the major reasons for the commercial success of relational databases SQL Structured Query Language Statements for data definitions, queries, and updates (both DDL and DML) Core specification Plus specialized extensions Copyright © 2011 Ramez Elmasri and Shamkant Navathe  SQL Data Definition and Data Types Terminology: Table, row, and column used for relational model terms relation, tuple, and attribute CREATE statement Main SQL command for data definition Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Schema and Catalog Concepts in SQL SQL schema Identified by a schema name Includes an authorization identifier and descriptors for each element Schema elements include Tables, constraints, views, domains, and other constructs Each statement in SQL ends with a semicolon Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Schema and Catalog Concepts in SQL (cont’d.) CREATE SCHEMA statement CREATE SCHEMA COMPANY AUTHORIZATION ‘Jsmith’; Catalog Named collection of schemas in an SQL environment SQL environment Installation of an SQL-compliant RDBMS on a computer system Copyright © 2011 Ramez Elmasri and Shamkant Navathe  The CREATE TABLE Command in SQL Specify a new relation Provide name Specify attributes and initial constraints Can optionally specify schema: CREATE TABLE COMPANY.EMPLOYEE... or CREATE TABLE EMPLOYEE... Copyright © 2011 Ramez Elmasri and Shamkant Navathe  The CREATE TABLE Command in SQL (cont’d.) Base tables (base relations) Relation and its tuples are actually created and stored as a file by the DBMS Loading… Virtual relations Created through the CREATE VIEW statement Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Attribute Data Types and Domains in SQL Basic data types Numeric data types Integer numbers: INTEGER, INT, and SMALLINT Floating-point (real) numbers: FLOAT or REAL, and DOUBLE PRECISION Character-string data types Fixed length: CHAR(n), CHARACTER(n) Varying length: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Attribute Data Types and Domains in SQL (cont’d.) Bit-string data types Fixed length: BIT(n) Varying length: BIT VARYING(n) Boolean data type Values of TRUE or FALSE or NULL DATE data type Ten positions Components are YEAR, MONTH, and DAY in the form YYYY-MM-DD Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Attribute Data Types and Domains in SQL (cont’d.) Additional data types Timestamp data type (TIMESTAMP) Includes the DATE and TIME fields Plus a minimum of six positions for decimal fractions of seconds. Optional WITH TIME ZONE qualifier INTERVAL data type Specifies a relative value that can be used to increment or decrement an absolute value of a date, time, or timestamp Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe  The CREATE TABLE Command in SQL (cont’d.) Some foreign keys may cause errors Specified either via: Circular references Or because they refer to a table that has not yet been created Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Attribute Data Types and Domains in SQL (cont’d.) Domain Name used with the attribute specification Makes it easier to change the data type for a domain that is used by numerous attributes Improves schema readability Example: CREATE DOMAIN SSN_TYPE AS CHAR(9); Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Specifying Constraints in SQL Basic constraints: Key and referential integrity constraints Restrictions on attribute domains and NULLs Constraints on individual tuples within a relation Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Specifying Attribute Constraints and Attribute Defaults NOT NULL NULL is not permitted for a particular attribute Default value DEFAULT CHECK clause Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21); Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Specifying Key and Referential Integrity Constraints PRIMARY KEY clause Specifies one or more attributes that make up the primary key of a relation Dnumber INT PRIMARY KEY; UNIQUE clause Specifies alternate (secondary) keys Dname VARCHAR(15) UNIQUE; Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Specifying Key and Referential Integrity Constraints (cont’d.) FOREIGN KEY clause Default operation: reject update on violation Attach referential triggered action clause Options include SET NULL, CASCADE, and SET DEFAULT Action taken by the DBMS for SET NULL or SET DEFAULT is the same for both ON DELETE and ON UPDATE CASCADE option suitable for “relationship” relations Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Giving Names to Constraints Keyword CONSTRAINT Name a constraint Useful for later altering Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Specifying Constraints on Tuples Using CHECK CHECK clauses at the end of a CREATE TABLE statement Apply to each tuple individually CHECK (Dept_create_date