1 / 20

Outline

Outline. DDL Creating/altering schema Data types Constraints DataArchitect mapping from a CDM to a PDM Referential integrity and other assertions. Referential Integrity. Referential integrity says “pointed to” information must exist. A foreign key points to data in some relation Example

tadeo
Download Presentation

Outline

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Outline • DDL • Creating/altering schema • Data types • Constraints • DataArchitect mapping from a CDM to a PDM • Referential integrity and other assertions The SQL Query Language DDL(2)

  2. Referential Integrity • Referential integrity says “pointed to” information must exist. • A foreign key points to data in some relation • Example • Customer information must exist for a customer to reserve a film • No CustomerID can be in Reserves and not in Customer • Can be specified as a column constraint CREATE TABLE Reserves (... CustomerID INTEGER CONSTRAINT ReservesToCustomerFK REFERENCES Customer(ID), ...) • Can be specified as a table constraint CREATE TABLE Reserves (..., CONSTRAINT ReservesToCustomerFK FOREIGN KEY (CustomerID) REFERENCES Customer(ID) ... ) The SQL Query Language DDL(2)

  3. Referential Integrity Violation Remedies • Can specify ON UPDATE and ON DELETE options • Example CREATE TABLE Reserves (..., CONSTRAINT ResToCusFK FOREIGN KEY (CustomerID) REFERENCES Customer(ID) ON DELETE CASCADE ON UPDATE CASCADE ... ) • Options (next slide) • Note: Child table - has the foreign key, references key in parent table • Example: Customer is parent, Reserves is child The SQL Query Language DDL(2)

  4. Remedy Options • None • Update or delete parent value • No change to matching child value • Restrict • Cannot update or delete parent value if one or more matching values exist in the child table • No change to matching child value • Cascade • Update or delete parent value • Update or delete matching values in child table The SQL Query Language DDL(2)

  5. Remedy Options, cont. • Set null • Update or delete parent value • Set matching values in child table to NULL • Set default • Update or delete parent value • Set matching values in child table to default value The SQL Query Language DDL(2)

  6. Cascade Remedy Example • Delete Customer with ID 2 Before Customer Reserves Name ID FilmID CID Fred 2 7 2 Pam 4 2 4 Fred 3 After Customer Reserves Name ID FilmID CID Pam 4 2 4 Fred 3 The SQL Query Language DDL(2)

  7. Cascade Remedy Example • Update Customer with ID 2, changing ID to 5 Before Customer Reserves Name ID FilmID CID Fred 2 7 2 Pam 4 2 4 Fred 3 After Customer Reserves Name ID FilmID CID Fred 5 7 5 2 4 Pam 4 Fred 3 The SQL Query Language DDL(2)

  8. Restrict Remedy Example • Update Customer with ID 2, changing ID to 5 Before Customer Reserves Name ID FilmID CID Fred 2 7 2 Pam 4 2 4 Fred 3 After Customer Reserves Name ID FilmID CID Fred 2 7 2 2 4 Pam 4 Fred 3 The SQL Query Language DDL(2)

  9. Set Null Remedy Example • Update Customer with ID 2, changing ID to 5 Before Customer Reserves Name ID FilmID CID Fred 2 7 2 Pam 4 2 4 Fred 3 After Customer Reserves Name ID FilmID CID Fred 5 7 @ 2 4 Pam 4 Fred 3 The SQL Query Language DDL(2)

  10. None Remedy Example • Update Customer with ID 2, changing ID to 5 • Update allowed, referential integrity is lost. Before Customer Reserves Name ID FilmID CID Fred 2 7 2 Pam 4 2 4 Fred 3 After Customer Reserves Name ID FilmID CID Fred 5 7 2 2 4 Pam 4 Fred 3

  11. Deferring Constraints • Constraints can optionally be deferred. • NOT DEFERRABLE • This is the default • The constraint is checked at the end of each SQL statement • If the constraint is violated, the enclosing transaction is aborted • DEFERRABLE • The constraint can be checked at the beginning of each transaction: • DEFERRABLE INITIALLY IMMEDIATE • Or it can be checked at the user's request: • DEFERRABLE INITIALLY DEFERRED • In that case, the user specifies checking with • SET CONSTRAINTnameIMMEDIATE

  12. Drop Table • Used to remove a base table and its definition • The table can no longer be used in queries, updates, or any other commands since its description no longer exists • Example:DROP TABLE Customer The SQL Query Language DDL(2)

  13. Alert Table • Used to • Add a column • Drop a column • Change a column’s default • Add a constraint • Drop a constraint • Example ALTER TABLE Film ADD COLUMN PurchasePrice NUMERIC (5,2) • The new attribute will have NULLs in all the tuples of the table immediately after the command is executed • Hence, the NOT NULL constraint is not allowed for such an attribute, unless a DEFAULT is specified • The database users must still enter a value for the new column PurchasePrice for each Film row The SQL Query Language DDL(2)

  14. Example, Movie World Conceptual DM The SQL Query Language DDL(2)

  15. Example, Physical Data Model STARSIN MOVIENAME = MOVIENAME STARNAME = STARNAME STARNAME VARCHAR(30) WHENMADE = WHENMADE MOVIENAME VARCHAR(50) WHENMADE DATETIME MOVIE STAR MOVIENAME VARCHAR(50) STARNAME VARCHAR(30) WHENMADE DATETIME AGE INT STUDIONAME VARCHAR(50) STUDIONAME = STUDIONAME STUDIO STUDIONAME VARCHAR(50) WHERE VARCHAR(50) The SQL Query Language DDL(2)

  16. Example, Create Star Table CDM PDM STAR STARNAME VARCHAR(30) AGE INT create table STAR ( STARNAME VARCHAR(30) not null, AGE INT null , constraint PK_STAR primary key (STARNAME)) SQL from PDM The SQL Query Language DDL(2)

  17. Example, Create Movie Table CDM MOVIE MOVIENAME VARCHAR(50) PDM WHENMADE DATETIME STUDIONAME VARCHAR(50) SQL from PDM create table MOVIE( MOVIENAME VARCHAR(50) not null, WHENMADE DATETIME not null, STUDIONAME VARCHAR(50) not null, constraint PK_MOVIE primary key (MOVIENAME, WHENMADE)) The SQL Query Language DDL(2)

  18. Example, Create STARSIN Table CDM PDM STARSIN MOVIENAME = MOVIENAME STARNAME = STARNAME STARNAME VARCHAR(30) WHENMADE = WHENMADE MOVIENAME VARCHAR(50) WHENMADE DATETIME create table STARSIN( STARNAME VARCHAR(30) not null, MOVIENAME VARCHAR(50) not null, WHENMADE DATETIME not null, constraint PK_STARSIN primary key (STARNAME, MOVIENAME, WHENMADE)) The SQL Query Language DDL(2)

  19. Example, Foreign Key Constraints CDM STARSIN MOVIENAME = MOVIENAME STARNAME = STARNAME STARNAME VARCHAR(30) WHENMADE = WHENMADE MOVIENAME VARCHAR(50) WHENMADE DATETIME MOVIE STAR MOVIENAME VARCHAR(50) PDM STARNAME VARCHAR(30) WHENMADE DATETIME AGE INT STUDIONAME VARCHAR(50) alter table STARSIN add constraint FK1 foreign key(STARNAME) references STAR (STARNAME) alter table STARSIN add constraint FK2 foreign key(MOVIENAME, WHENMADE) references MOVIE (MOVIENAME, WHENMADE) The SQL Query Language DDL(2)

  20. Summary of Table Modification • CREATE TABLE • Name • Columns • Name • Data type • Column constraints • Table constraints • DROP TABLE • ALTER TABLE The SQL Query Language DDL(2)

More Related