Introduction to Database Review 2 Crows-Feet Notation

Introduction to Database Review 2

Crows-Feet Notation for ER Diagrams q This is an alternative to the diamond representation of relationships. q Diamond icons are replaced with lines, simplifying the ER schema. Zero or one One or more Entity 1 Entity 2 q Intuition § § § Zero or more Exactly one (mandatory) means “Zero” means “One” means “or more” Entity 3

Subclasses And Superclasses q Grouping of the entities of an entity type into subgroups (forming an "IS-A" relationship). q Entities in a superclass are grouped into one or more subclasses. q An arbitrary number of levels is permitted in a class hierarchy. q An entity in a subclass exists in the superclass also (recursively).

Subclasses/Superclasses Example q The Customer entity type has the subclasses Preferred. Customer and Employee. q All employees are customers. Customer O Employee Preferred Customer

Inheritance Among Classes q Entities in a subclass inherit the attributes of the superclass. q A subclass may have its own attributes (termed the specific attributes). q Entities in a subclass may participate in relationships directly (termed specific relationships), and they may participate in relationships via their superclass(es).

Inheritance Example q A Preferred. Customer entity inherits the attributes Name, Address, Cutomer. ID and Balance from Customer. q The Preferred. Customer subclass also has the attribute Discount. Level. Address Name Customer. ID Balance O Employee. ID Discount Level Employee Preferred Customer 6

Schema Design Strategies q Top-down strategy § Start out with high-level, abstract concepts and apply step-wise refinements (e. g. , specialization) to add "detail. " q Bottom-up strategy § Start out with basic concepts and apply refinements (e. g. , generalization). q Inside-out strategy § Start with a few central concepts and successively include additional concepts. q Mixed strategy § Combine the above strategies.

Top-down Strategy Customer. ID Name Length Customer (0, m) Checked Out (0, n) Title Video. Tape. Num Customer. ID Name (0, n) Rental. Price Status Video. Tape Length (1, 1) Copies Title Film. ID Rental. Price (0, n) Film

Bottom-up Strategy q Example: discovering a new generalized entity type and relating it. Length Customer. ID Customer Name Employee (0, m) Cust Checked Out Employee. ID Length Rental. Price (0, n) Video. Tape (0, n) Title

Bottom-up Strategy, cont. q This is converted to: Length Customer. ID Name Rental. Price Customer (0, m) O Employee. ID Preferred Customer Checked Out (0, n) Video. Tape Title

Conceptual Database Design Approaches q Centralized design approach § Integrate first the requirements for all applications and then design a single schema. § Assumes a centralized organization. § The DBA merges the multiple sets of requirements. § The DBA designs the schema. q View integration approach § Design first a schema for each application in isolation, then integrate the schemas into a single global schema. § Each user group can design its own schema. § The DBA designs the global schema.

Entity Integrity q Primary Key: A candidate key of a relation is a set of attributes that satisfy two time independent properties: § Uniqueness - No two tuples of the relation have the same values for the set of attributes forming the candidate key. § Minimality - No attributes can be discarded from the candidate key without destroying the uniqueness property. q No component of the Primary Key of a base relation is allowed to accept nulls.

Foreign key q A foreign key is an attribute or attribute combination of one relation R 2 whose values are required to match those of the primary key of relation R 1 where R 1 and R 2 are not necessarily distinct. Note that a foreign key and the corresponding primary key should be defined on the same domain(s). Employee Emp# e 1 e 2 e 3 ename red blue brown Dept Worksfordept d 1 Foreign key d 2 Dept d 1 d 2 d 3 Dname Pay Tax Art

Mapping an EER Schema to Relations q In a sequence of steps, a set of relations is created. q Sometimes automated in CASE tools 1. 2. 3. 4. 5. 6. 7. 8. Regular entity types Weak entity types Binary 1: 1 relationship types Binary 1: N relationship types Binary M: N relationship types n-ary relationship types Multi-valued attributes Superclass/subclass relationship types

1. Entity Type Maps to a Table q Create a table for each regular entity type. § One column in table for each simple attribute § Derived attributes may or may not appear (your choice) § Table’s primary key is the primary key of the entity type q Optimization: If there are no attributes other than the primary key, and if the entity participates totally in a relationship, then the table can be eliminated.

2. Weak Entity Type Maps to a Table q Create a table for each weak entity type § One column for each simple attribute § Include column(s) for the primary key of each owner entity type. These columns are foreign keys § The primary key is the combination of each owner primary key and the partial key.

3. Mapping 1 -1 Relationship Types q For each 1: 1 binary relationship type, extend one of the tables for a participating entity type. § Primary key of the other entity type becomes a foreign key in this table q It is best to extend a table of an entity type with total participation q Add columns for each of the simple attributes of the relationship type q Optimization: Perhaps remove the table corresponding to the other entity type

4. 1 -to-Many Relationship Types q For each regular 1: N binary relationship type, there are several approaches § Option 1: Create a separate table for the relationship type ü Three tables result ü Key of relationship table is key of “many” side § Option 2: If the relationship is total, then extend a table corresponding to the ‘many’ entity type ü Two tables result (optimization) § Option 3: If the relationship is not total, extend a table with nullable attributes (sometimes not allowed foreign keys) ü Two tables result (optimization)

5. Many-to-Many Relationship Types q Create a table for each binary M: N relationship type q The table has columns for § A column for each primary key attribute in a participating entity type. These are foreign keys § A column for each of the simple attribute of the relationship type q The primary key of the table is the union of the primary keys of the participating entity types

6. N-ary Relationship Types q Create a table for each n-ary (n > 2) relationship type § Columns in the table are the primary keys of the participating entity types. (These are foreign keys) § Also include columns for each simple attribute of the relationship type q The primary key of the created table is the union of the primary keys of the participating entity types q Optimization: If the relationship type is (1, 1) on a side, it may be possible to remove an entity table, placing its attributes in the table associated with the relationship

7. Multivalued Attributes q Create a table for each multivalued attribute § The table has a column for each simple attribute of the multivalued attribute § Add columns for the primary key of the entity or relationship type to which the attribute belongs. (This is a foreign key) q The primary key is the combination of all the attributes q Example: Director Film q Director (Film. ID, Name) Film. ID

Outline q DDL § § § Creating/altering schema Data types Constraints Data. Architect mapping from a CDM to a PDM Referential integrity and other assertions

Data Definition in SQL q Three statements are used to define the schema in SQL. § CREATE § DROP § ALTER q These statements apply to § Tables § Views § Domains

Create Table q Specifies a new base table CREATE TABLE <table name> [<size>] <column constraint>, q Columns with § § Name Data type Column constraints Default value q Table constraints (<column name> <data type>. . . <table constraints> );

Referential Integrity q Referential integrity says “pointed to” information must exist. § A foreign key points to data in some relation q Example § Customer information must exist for a customer to reserve a film § No Customer. ID can be in Reserves and not in Customer q Can be specified as a column constraint CREATE TABLE Reserves (. . . Customer. ID INTEGER CONSTRAINT Reserves. To. Customer. FK REFERENCES Customer(ID), . . . ) q Can be specified as a table constraint CREATE TABLE Reserves (. . . , CONSTRAINT Reserves. To. Customer. FK FOREIGN KEY (Customer. ID) REFERENCES Customer(ID). . . )

Referential Integrity Violation Remedies q Can specify ON UPDATE and ON DELETE options q Example CREATE TABLE Reserves (. . . , CONSTRAINT Res. To. Cus. FK FOREIGN KEY (Customer. ID) REFERENCES Customer(ID) ON DELETE CASCADE ON UPDATE SET NULL. . . ) q Options (next slide) § Note: Child table - has the foreign key, references key in parent table § Example: Customer is parent, Reserves is child

Remedy Options q None § Update or delete parent value § No change to matching child value q Restrict § Cannot update or delete parent value if one or more matching values exist in the child table § No change to matching child value q Cascade § Update or delete parent value § Update or delete matching values in child table

Remedy Options, cont. q Set null § Update or delete parent value § Set matching values in child table to NULL q Set default § Update or delete parent value § Set matching values in child table to default value

Retrieval Queries in SQL: SELECT q SQL has one basic statement for retrieving information from a database; the SELECT statement. q The basic form of the SQL SELECT statement is called a mapping or a select-from-where block. SELECT column list FROM table list WHERE condition

Outline - The SELECT statement q Single table § Projection § Selection q Multiple tables § Cartesian product and join § Set operations § Subqueries q Optional clauses § Ordering results § Computing aggregates on groups q Additional joins

Modifications q There are three modification statements § INSERT § UPDATE § DELETE q For insertions, either values can be specified, or a select statement provides the values q Enter a reservation for Eric for the film 332244 INSERT INTO Reserved VALUES (123456, 332244, CURRENT_DATE)

View q Views provide a mechanism to create a virtual table CREATE VIEW name AS query expression q To create a view we use the command q Define a view of all customers in Dublin CREATE VIEW Dublin_Customers AS SELECT * FROM Customer WHERE City = ’Dublin’

View, cont. q Define a view of all customers holding reservations, and the films they have reserved CREATE VIEW Reservations AS SELECT Name, Title FROM Customer, Reserved, Film WHERE Customer. ID = Reserved. Customer. ID AND Reserved. Film. ID = Film. ID

Transactions q A transaction can be defined syntactically: each transaction, irrespective of the language in which it is written, is enclosed whthin two commands begin transaction end transaction q Within the transaction code, two particular instructions can appear commit work rollback work

Triggers q The creation of triggers is part of the DDL q Maintain data integrity q Associated with a table (view) q Event-condition-action § Wait for a table event before after X insertion deletion update § On event, evaluate condition § If condition is true, execute action

Potential Applications q Notification § an active database may be used to monitor q Enforce integrity constraints § Business roles q Maintenance of derived data § Maintain the derived attribute whenever individual tuples are changed

Trigger Gotchas q Potentially infinite loop § Trigger A: On insertion into Person, insert into Population § Trigger B: On insertion into Population, insert into Person q Mutating tables § Trigger A: On insertion into Person, insert into Person! § Disallowed! § Trigger cannot make changes to table that trigger is defined on

The Object Database q Object databases integrate database technology with the object-oriented paradigm q In object databases, each entity of the real world is represented by an object. Classical examples of objects are: § Electronic components, designed using a Computer Aided Design (CAD) system; § Mechanical components, designed using a Computer Aided Manufacturing (CAM) system; § Specifications and programs, managed in a Computer Aided Software Engineering (CASE) environment; § Multimedia documents, which includes text, images and sound, managed by multimedia document managers.

Why OODB? q From programming language point of view: § permanent storage of objects (languages just support objects in memory) § sharing of objects among programs § fast, expressive queries for accessing data § version control for evolving classes and multi-person projects

Why OODB? q From database point of view: § More expressive data types (traditional DBs provide limited predefined types) ü e. g. , a desktop publishing program might model a page as a series of frames containing text, bitmaps, and charts ü need composite and aggregate data types (e. g. , structures and arrays) § More expressive data relationships ü many-to-one relationship (e. g. , many students in one class) ü navigating across relationship links § More expressive data manipulation ü SQL is relationally complete but not computationally complete i. e. , great for searching for lousy for anything else – leads to use of conventional programming language plus SQLinterface – overhead of mapping from SQL to conventional languages ü Better integration with programming languages (esp. OO languages) ü Encapsulation of code with data 40

Two Object-oriented Approaches q Object-oriented (OODBMS) § Hellerstein - “to add DBMS capabilities to an O-O language” § Persistence, object lives beyond program execution ü PJava - persistent Java ü Several commercial products q Object-relational (ORDBMS) § Hellerstein - “extends a relational database with O-O features” § Rich data types ü Inheritance ü Several commercial vendors, SQL 3

OODBMS q Advantages § Removes impedance mismatch § Long-lived transactions § Enriched modeling q Disadvanatages § Lack of universal query language § Lack of agreed upon standard § Performance depends on class definition