Introduction to Data Management: Lecture 16, Lecture notes of Database Management Systems (DBMS)

Download Introduction to Data Management: Lecture 16 and more Lecture notes Database Management Systems (DBMS) in PDF only on Docsity!

Introduction to Data Management

CSE 344

Lecture 16: Constraints

Announcements

• WQ6 due Thursday (there is no WQ5…)
• Homework 4 posted, due Friday
• Midterm: Monday, November 4 th

, in class

Where We Are?

We are learning about database design

• How to design a database schema?
• Last time: Real world -> ER Diagrams -> Relations Next, we will learn more about good schemas
• Today: Constraints and data integrity
• Next time: Schema normalization, then Views

Person Company Product buys makes employs name CEO price address name ssn address name 5

Weak Entity Sets

Team University affiliation sport number name Team(sport, number, universityName) University(name) What does this say?

What Are the Keys of R?

R

A B S T V Q

W U

V

Z

C

D

E

G

K

H

F

L

Constraints in E/R Diagrams

Finding constraints is part of the modeling process. Commonly used constraints: Keys: social security number uniquely identifies a person. Single-value constraints: a person can have only one father. Referential integrity constraints: if you work for a company, it must exist in the database. Other constraints: peoples’ ages are between 0 and 150.

Keys in E/R Diagrams

address name ssn Person Product name category price No formal way to specify multiple keys in E/R diagrams Underline: 11

Referential Integrity Constraints

Product Company makes Product Company makes Each product made by at most one company. Some products made by no company Each product made by exactly one company.

Other Constraints

Product Company makes

Q: What does this mean? A: A Company entity cannot be connected by relationship to more than 99 Product entities

Key Constraints

OR:

CREATE TABLE Product ( name CHAR(30) PRIMARY KEY, category VARCHAR(20)) CREATE TABLE Product ( name CHAR(30), category VARCHAR(20) PRIMARY KEY (name)) Product(name, category)

Keys with Multiple Attributes

CREATE TABLE Product ( name CHAR(30), category VARCHAR(20), price INT, PRIMARY KEY (name, category)) Name Category Price Gizmo Gadget 10 Camera Photo 20 Gizmo Photo 30 Gizmo Gadget 40 Product(name, category, price)

Foreign Key Constraints

CREATE TABLE Purchase ( prodName CHAR(30) REFERENCES Product(name), date DATETIME) prodName is a foreign key to Product(name) name must be a key in Product Referential integrity constraints May write just Product if name is PK

Name Category Gizmo gadget Camera Photo OneClick Photo ProdName Store Gizmo Wiz Camera Ritz Camera Wiz Product Purchase

Foreign Key Constraints