1 / 15

Data Models Example: A relation is a table.

Attributes (column headers). Attributes (column headers). Tuples (rows). Tuples (rows). Data Models Example: A relation is a table. name manf Winterbrew Pete’s Bud Lite Anheuser-Busch Beers. Relation name. Relation name. Schemas versus Instances.

lexiss
Télécharger la présentation

Data Models Example: A relation is a table.

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. Attributes (column headers) Attributes (column headers) Tuples (rows) Tuples (rows) Data ModelsExample: A relation is a table. name manf Winterbrew Pete’s Bud Lite Anheuser-Busch Beers Relation name Relation name

  2. Schemas versus Instances • Database Schema: The description of a database. Includes descriptions of the database structure and the constraints that should hold on the database. • Relation schema = relation name and attribute list. • Optionally: types of attributes. • Example: Beers(name, manf) or Beers(name: string, manf: string) • Database = collection of relations. • Database schema = set of all relation schemas in the database.

  3. Example Student Database instance Section Grade Report Suppose we have the following information in our database: Slide 1-3

  4. Example Student Section Grade Report Schema diagram Schema constructs Slide 1-4 Suppose we have the following information in our database:

  5. Example of a Relation

  6. Formal Definitions - Schema • The Schema (or description) of a Relation: • Denoted by R(A1, A2, .....An) • R is the name of the relation • The attributes of the relation are A1, A2, ..., An • Example: CUSTOMER (Cust-id, Cust-name, Address, Phone#) • CUSTOMER is the relation name • Defined over the four attributes: Cust-id, Cust-name, Address, Phone# • Each attribute has a domain or a set of valid values. • For example, the domain of Cust-id is 6 digit numbers.

  7. Formal Definitions - Tuple • A tuple is an ordered set of values (enclosed in angled brackets ‘< … >’) • Each value is derived from an appropriate domain. • A row in the CUSTOMER relation is a 4-tuple and would consist of four values, for example: • <632895, "John Smith", "101 Main St. Atlanta, GA 30332", "(404) 894-2000"> • This is called a 4-tuple as it has 4 values • A tuple (row) in the CUSTOMER relation. • A relation is a set of such tuples (rows)

  8. Key Constraints (continued) • If a relation has several candidate keys, one is chosen arbitrarily to be the primary key. • The primary key attributes are underlined. • Example: Consider the CAR relation schema: • CAR(State, Reg#, SerialNo, Make, Model, Year) • We chose SerialNo as the primary key • The primary key value is used to uniquely identify each tuple in a relation • Provides the tuple identity • Also used to reference the tuple from another tuple • General rule: Choose as primary key the smallest of the candidate keys (in terms of size) • Not always applicable – choice is sometimes subjective

  9. Referential Integrity • Tuples in the referencing relation R1 have attributes FK (called foreign key attributes) that reference the primary key attributes PK of the referenced relation R2. • A tuple t1 in R1 is said to reference a tuple t2 in R2 if t1[FK] = t2[PK]. • A referential integrity constraint can be displayed in a relational database schema as a directed arc from R1.FK to R2.

  10. Relational Database Schema • Relational Database Schema: • A set S of relation schemas that belong to the same database. • S is the name of the whole database schema • S = {R1, R2, ..., Rn} • R1, R2, …, Rn are the names of the individual relation schemas within the database S • Following slide shows a COMPANY database schema with 6 relation schemas

  11. COMPANY Database Schema

  12. Referential Integrity Constraints for COMPANY database

  13. CREATE TABLE • In SQL2, you can use the CREATE TABLE command for specifying the primary key attributes, secondary keys, and referential integrity constraints (foreign keys). • Key attributes can be specified via the PRIMARY KEY and UNIQUE phrases CREATE TABLE DEPT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9), PRIMARY KEY (DNUMBER), UNIQUE (DNAME), FOREIGN KEY (MGRSSN) REFERENCES EMP );

  14. Assignment • Define at least five tables with formal schema definition(as given in slide 6 orslide 5-6 • At least fourof these tables should be connected to other tables with foreign key relation • Show these tables with referential integrity constraints(like in slide 14) • Create each tables SQL queries with CREATE table statement

  15. Assignment • Important points • Due Date • For Wednesday Class March 9th • For Thursday Class March 10th • For Friday Class March 11th • Each homework will be delivered in lab lectures • Late delivery will not be tolerated • Homeworkswill be deliveredonpaper, emailsubmissionwill not be accepted • Keep a copy of your assignment, we will continue to use our tables in next assignments

More Related