Prepare for your exams
Get points
Guidelines and tips
Sell on Docsity
Docsity AI

Prepare for your exams

Study with the several resources on Docsity

Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips

Sell on Docsity

Docsity AI

Log in Sign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search for your university

Find the specific documents for your university's exams

Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents

20 Points

For each uploaded document

Answer questions

5 Points

For each given answer (max 1 per day)

All the ways to get free points

Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

Introduction to Database Systems-Lecture 03 Slides-Computer Science, Slides of Introduction to Database Management Systems

Duke University Introduction to Database Management Systems

Relational Database Design, Relational Model, Keys, Schema Vs. Data, Usage of Keys, Look up, Pointers, Database Design, Entity-relationship Model, E/R Basics, Attributes of Relationships, Multiplicity of Relationships, N-ary Relationships, N-ary Versus Binary Relationships, Roles in Relationships, Weak Entity Sets, Many-one, One-one, Modeling N-ary Relationships, ISA Relationships, Redundancy, Reference

Typology: Slides

2011/2012

Uploaded on 01/29/2012

arold 🇺🇸

4.7

(24)

372 documents

1 / 5

This page cannot be seen from the preview

Don't miss anything!

1

Relational Database Design

Part I

CPS 116

Introduction to Database Systems

2

Announcement

DB2 accounts created; change your password!

Let me know if you have NOT received the email

Pick up Gradiance registration information sheet at

the end of this lecture

Homework #1 out today

Due next Thursday (September 9) at 11:59pm

)Start early!

3

Relational model: review

A database is a collection of relations (or tables)

Each relation has a list of attributes (or columns)

Each attribute has a domain (or type)

Each relation contains a set of tuples (or rows)

4

Keys

A set of attributes Kis a key for a relation Rif

In no instance of Rwill two different tuples agree on all

attributes of K

•That is, Kis a “tuple identifier”

No proper subset of Ksatisfies the above condition

•That is, Kis minimal

Example: Student (SID, name, age, GPA)

SID is a key of Student

age is not a key (not an identifier)

{SID, name} is not a key (not minimal)

5

Schema vs. data

Is name a key of Student?

Yes? Seems reasonable for this instance

No! Student names are not unique in general

Key declarations are part of the schema

Student

SID name age GPA

142 Bart 10 2.3

123 Milhouse 10 3.1

857 Lisa 8 4.3

456 Ralph 8 2.3

6

More examples of keys

Enroll (SID, CID)

{SID, CID}

Address (street_address, city, state, zip)

{street_address, city, state}

{street_address, zip}

Discover Slides of Introduction to Database Management Systems Duke University

Partial preview of the text

Download Introduction to Database Systems-Lecture 03 Slides-Computer Science and more Slides Introduction to Database Management Systems in PDF only on Docsity!

Relational Database Design

Part I

CPS 116

Introduction to Database Systems

Announcement

DB2 accounts created; change your password!

Let me know if you have NOT received the email

Pick up Gradiance registration information sheet at

the end of this lecture

Homework #1 out today

Due next Thursday (September 9) at 11:59pm

)Start early!

3

Relational model: review

A database is a collection of relations (or tables)

Each relation has a list of attributes (or columns)

Each attribute has a domain (or type)

Each relation contains a set of tuples (or rows)

4

Keys

A set of attributes K is a key for a relation R if

In no instance of R will two different tuples agree on all

attributes of K

That is, K is a “tuple identifier”

No proper subset of K satisfies the above condition

That is, K is minimal

Example: Student ( SID , name , age , GPA )

SID is a key of Student

age is not a key (not an identifier)

{ SID , name } is not a key (not minimal)

5

Schema vs. data

Is name a key of Student?

Yes? Seems reasonable for this instance

No! Student names are not unique in general

Key declarations are part of the schema

Student SID 142 (^) name Bart (^) age 10 GPA 2. 123857 Milhouse 10Lisa 8 3.14. 456 Ralph 8 2.

6

More examples of keys

Enroll ( SID , CID )

{ SID , CID }

Address ( street_address , city , state , zip )

{ street_address , city , state }

{ street_address , zip }

Usage of keys

More constraints on data, fewer mistakes

Look up a row by its key value

Many selection conditions are “key = value”

“Pointers”

Example: Enroll ( SID , CID )

SID is a key of Student
CID is a key of Course
An Enroll tuple “links” a Student tuple with a Course tuple

Many join conditions are “key = key value stored in

another table”

Database design

Understand the real-world domain being modeled

Specify it using a database design model

Design models are especially convenient for schema

design, but are not necessarily implemented by DBMS

Popular ones include

Entity/Relationship (E/R) model
Object Definition Language (ODL)

Translate specification to the data model of DBMS

Relational, XML, object-oriented, etc.

Create DBMS schema

9

Entity-relationship (E/R) model

Historically and still very popular

Can think of as a “watered-down” object-oriented

design model

E/R diagrams represent designs

Primarily a design model—not implemented by any

major DBMS

10

E/R basics

Entity: a “thing,” like a record or an object

Entity set: a collection of things of the same type, like a

relation of tuples or a class of objects

Represented as a rectangle

Relationship: an association among entities

Relationship set: a set of relationships of the same type

(associations among same entity sets)

Represented as a diamond

Attributes: properties of entities or relationships, like

attributes of tuples or objects

Represented as ovals

11

An example E/R diagram

Students enroll in courses

A key of an entity set is represented by underlining

all attributes in the key

A key is a set of attributes whose values can belong to at

most one entity in an entity set—like a key of a relation

nameSID^ Students^ Enroll Courses^ CIDtitle

12

Attributes of relationships

Example: students take courses and receive grades

Students Courses CID title

SID Enroll name grade

Where do the grades go?

With Students?

But a student can have different grades for multiple courses

With Courses?

But a course can assign different grades for multiple students

With Enroll!

Weak entity set examples

Seats in rooms in buildings

Rooms In Buildings name year

number capacity In

Seats

number L/R?

Strictly speaking, the two arrows in this diagram should be rounded

Modeling n -ary relationships

An n -ary relationship set can be replaced by a weak

entity set (called a connecting entity set) and n

binary relationship sets

Students Enroll Courses TA’s

TA’s

Students Enroll Courses

Note that the multiplicity constraint is lost

21

ISA relationships

Similar to the idea of subclasses in object-oriented

programming: subclass = special case, more

properties, and fewer entities

Represented as a triangle (direction is important)

Example: Graduate students are students, but they

also have offices

Students Courses CID title

SID Enroll name

office GradStudents

ISA

22

Summary of E/R concepts

Entity sets

Keys

Weak entity sets

Relationship sets

Attributes of relationships

Multiplicity

Roles

Binary versus N -ary relationships

Modeling N -ary relationships with weak entity sets and binary relationships

ISA relationships

23

Case study 1

Design a database representing cities, counties, and states

For states, record name and capital (city) For counties, record name, area, and location (state) For cities, record name, population, and location (county and state)

Assume the following:

Names of states are unique Names of counties are only unique within a state Names of cities are only unique within a county A city is always located in a single county A county is always located in a single state

24

Case study 1: first design

County area information is repeated for every city in

the county

)Redundancy is bad (why?)

State capital should really be a city

)“Reference” entities through explicit relationships

Cities In States

name capital

name population

county_area

county_name

Case study 1: second design

Technically, nothing in this design could prevent a

city in state X from being the capital of another

state Y , but oh well…

Cities IsCapitalOf

name population

Counties

name area name

In In States

Case study 2

Design a database consistent with the following:

A station has a unique name and an address, and is either

an express station or a local station

A train has a unique number and an engineer, and is

either an express train or a local train

A local train can stop at any station

An express train only stops at express stations

A train can stop at a station for any number of times

during a day

Train schedules are the same everyday

27

Case study 2: first design

Nothing in this design prevents express trains from

stopping at local stations

)Capture all constraints if possible

A train can stop at a station only once during a day

)Do not introduce constraints

Trains StopsAt Stations

name address

number

E/L?

engineer E/L? (^) time

28

Case study 2: second design

Trains Stations

name address

number engineer

time

ExpressTrains

LocalTrains LocalStations ExpressStations

ISA

LocalTrainStops ISA

time ExpressTrainStops Is the extra complexity worth it?

Introduction to Database Systems-Lecture 03 Slides-Computer Science, Slides of Introduction to Database Management Systems

Related documents

Partial preview of the text

Download Introduction to Database Systems-Lecture 03 Slides-Computer Science and more Slides Introduction to Database Management Systems in PDF only on Docsity!

Relational Database Design

Part I

CPS 116

Introduction to Database Systems

Announcement

 DB2 accounts created; change your password!

 Let me know if you have NOT received the email

 Pick up Gradiance registration information sheet at

the end of this lecture

 Homework #1 out today

 Due next Thursday (September 9) at 11:59pm

)Start early!

Relational model: review

 A database is a collection of relations (or tables)

 Each relation has a list of attributes (or columns)

 Each attribute has a domain (or type)

 Each relation contains a set of tuples (or rows)

Keys

 A set of attributes K is a key for a relation R if

 In no instance of R will two different tuples agree on all

attributes of K

 No proper subset of K satisfies the above condition

 Example: Student ( SID , name , age , GPA )

 SID is a key of Student

 age is not a key (not an identifier)

 { SID , name } is not a key (not minimal)

Schema vs. data

 Is name a key of Student?

 Yes? Seems reasonable for this instance

 No! Student names are not unique in general

 Key declarations are part of the schema

More examples of keys

 Enroll ( SID , CID )

 { SID , CID }

 Address ( street_address , city , state , zip )

 { street_address , city , state }

 { street_address , zip }

Usage of keys

 More constraints on data, fewer mistakes

 Look up a row by its key value

 Many selection conditions are “key = value”

 “Pointers”

 Example: Enroll ( SID , CID )

 Many join conditions are “key = key value stored in

another table”

Database design

 Understand the real-world domain being modeled

 Specify it using a database design model

 Design models are especially convenient for schema

design, but are not necessarily implemented by DBMS

 Popular ones include

 Translate specification to the data model of DBMS

 Relational, XML, object-oriented, etc.

 Create DBMS schema

Entity-relationship (E/R) model

 Historically and still very popular

 Can think of as a “watered-down” object-oriented

design model

 E/R diagrams represent designs

 Primarily a design model—not implemented by any

major DBMS

E/R basics

 Entity: a “thing,” like a record or an object

 Entity set: a collection of things of the same type, like a

relation of tuples or a class of objects

 Relationship: an association among entities

 Relationship set: a set of relationships of the same type

(associations among same entity sets)

 Attributes: properties of entities or relationships, like

attributes of tuples or objects

An example E/R diagram

 Students enroll in courses

 A key of an entity set is represented by underlining

all attributes in the key

 A key is a set of attributes whose values can belong to at

most one entity in an entity set—like a key of a relation

DB2 accounts created; change your password!

Let me know if you have NOT received the email

Pick up Gradiance registration information sheet at

Homework #1 out today

Due next Thursday (September 9) at 11:59pm

A database is a collection of relations (or tables)

Each relation has a list of attributes (or columns)

Each attribute has a domain (or type)

Each relation contains a set of tuples (or rows)

A set of attributes K is a key for a relation R if

In no instance of R will two different tuples agree on all

No proper subset of K satisfies the above condition

Example: Student ( SID , name , age , GPA )

SID is a key of Student

age is not a key (not an identifier)

{ SID , name } is not a key (not minimal)

Is name a key of Student?

Yes? Seems reasonable for this instance

No! Student names are not unique in general

Key declarations are part of the schema

Enroll ( SID , CID )

{ SID , CID }

Address ( street_address , city , state , zip )

{ street_address , city , state }

{ street_address , zip }

More constraints on data, fewer mistakes

Look up a row by its key value

Many selection conditions are “key = value”

“Pointers”

Example: Enroll ( SID , CID )

Many join conditions are “key = key value stored in

Understand the real-world domain being modeled

Specify it using a database design model

Design models are especially convenient for schema

Popular ones include

Translate specification to the data model of DBMS

Relational, XML, object-oriented, etc.

Create DBMS schema

Historically and still very popular

Can think of as a “watered-down” object-oriented

E/R diagrams represent designs

Primarily a design model—not implemented by any

Entity: a “thing,” like a record or an object

Entity set: a collection of things of the same type, like a

Relationship: an association among entities

Relationship set: a set of relationships of the same type

Attributes: properties of entities or relationships, like

Students enroll in courses

A key of an entity set is represented by underlining

A key is a set of attributes whose values can belong to at

Example: students take courses and receive grades

Where do the grades go?

With Students?

With Courses?

With Enroll!

Seats in rooms in buildings

An n -ary relationship set can be replaced by a weak

Similar to the idea of subclasses in object-oriented

Represented as a triangle (direction is important)

Example: Graduate students are students, but they

Entity sets

Keys

Weak entity sets

Relationship sets

Attributes of relationships

Multiplicity

Roles

Binary versus N -ary relationships

ISA relationships

Design a database representing cities, counties, and states

Assume the following:

County area information is repeated for every city in

State capital should really be a city

Technically, nothing in this design could prevent a

Design a database consistent with the following:

A station has a unique name and an address, and is either

A train has a unique number and an engineer, and is

A local train can stop at any station

An express train only stops at express stations

A train can stop at a station for any number of times