Introduction to Database Systems: Chapter 3 Practice Exercises and Solutions, Thesis of Deductive Database Systems

Download Introduction to Database Systems: Chapter 3 Practice Exercises and Solutions and more Thesis Deductive Database Systems in PDF only on Docsity!

Riyadh Philanthropic Society For Science

Prince Sultan College For Woman

Dept. of Computer & Information Sciences

CS 340

Introduction to Database Systems

(Chapter 3 Practice Exercises)

• Exercise 3.

Exercise 3.23 (Solution)

d. Relationship types and the (min,max) constraints:

BRANCHES

ACCTS

LOANS

A-C

L-C

BANK

BANK-BRANCH

BANK-BRANCH

ACCOUNT

LOAN

BANK-BRANCH

ACCOUNT

LOAN

CUSTOMER

CUSTOMER

BRANCHES

LOANS

A-C

ACCTS

L-C

(1,n)

(0,n)

(0,n)

(1,n)

(1,n)

(0,n)

(0,n)

Exercise 3.23 (Solution)

e. User requirements:

Each bank has a unique code, name, and address.

Each bank is related to 1 or more bank branches.

Each bank branch has an address and a branch number which is

unique among each set of bank branches that are related to the

same bank.

Each bank branch has 0 or more loans and 0 or more accounts.

Each account has an unique account number, balance, and type.

Each account is related to exactly 1 bank branch and to at least

1 customer.

Each loan has a unique loan number, amount, and type.

Each loan is related to 1 bank branch and to at least 1 customer.

Each customer has a SSN, name, phone, and address.

Each customer is related to 0 or more accounts and to 0 or more

loans.

Exercise 2

Identify the entities and relationships for the following description

. and draw an ER diagram

Persons, described by their name, SSN, and address, subscribe to

various journals. Each journal, identified by a title and an ISBN, has

a set of numbered volumes and each of these has a set of numbered

issues. Subscribers have an initial subscription date and a termination

. date for each journal to which they subscribe

Exercise 2 (solution)

PERSON SUBSCRIBE JORNAL

TO -

HAS

SSN

Address

Name ISBN

Title

ISSUE VOLUME VolumeNo

IssueNo

M N

N

CONTAINED-IN

N 1

InitialDate

TerminationDate

DEPARTMENT PRINTER

OPERATES

SUPPLIED

BY -

DId PrinterId

SUPPLIER

ROOM

BuildingNo

RoomNo

SName

IN

1 N

M

N

N

Exercise 3 (solution)

Delay

Charge

Exercise 4

. Draw an ER diagram for the following situation

State any assumptions you believe you have to make in order to

. develop a complete diagram

Stillwater Antiques buys and sells one-of-a-kind antiques of all kinds

(for example, furniture, jewelry, china, and clothing). Each item is

uniquely identified by an item number and is also characterized by a

description, asking price, condition, and open-ended comments.

Stillwater works with many different individuals, called clients, who

sell items to and buy items from the store. Some clients only sell

items to Stillwater, some only buy items, and some others both sell

and buy. A client is identified by a client number and is also

. described by a client name and client address

Exercise 4 (Solution)

ITEM

CLIENT

SoldTo

ItemNumber Description ClientNumber

Condition Comments

Price

Bought

From

Name

Address

SellingPrice Commission Tax DateSold

Cost DateBurchased Condition

N

N

Exercise 5

. Draw an ER diagram for the following situation

State any assumptions you believe you have to make in order to

. develop a complete diagram

Projects, Inc., is an engineering firm with approximately 500

employees. A database is required to keep track of all employees,

projects assigned, and departments worked in. every employee has a

unique number assigned by the firm, required to store his or her name

and date of birth. If an employee is currently married to another

employee of projects, Inc., the date of marriage and who is married to

whom must be stored; however, no record of marriage is required if

an employee’s spouse is not also an employee. Each employee is

. given a job title (for example, engineer, secretary, and so on)

Exercise 5 (Solution)

DEPARTMENT VENDOR

BuyesFrom

belongsTo

DeptName

Phone

VendorName

EMPLOYEE PROJECT

Title

M N

N

WorksOn

M N

LastMeeting

Address

ProjName

Cost

MarriedTo

DateMarried

EmpName BirthDate

EmpNumber

Exercise 6

: Consider the following information about a university database

Professors have a SSN, a name, an age, a rank, and a research •

. specialty

Projects have a project number, a sponsor name (e.g. NFS), a •

. starting date, an ending date, and a budget

Graduate students have a SSN, a name, an age, and a degree •

. program (e.g., M.S. or Ph.D.)

Each project is managed by one professor (known as the project's •

). principal investigator

Each project is worked on by one or more professors (known as the •

). project's co-investigators

.Professors can manage and/or work on multiple projects •

Exercise 6

Graduate students have one major department in which they are •

. working on their degree

Each graduate student has another, more senior graduate student •

who advises him or her on what) known as a student advisor (

. courses to take

Draw an ER diagram that captures the information about the

. university

Exercise 6 (solution)

PROFESSOR PROJECT

MANAGES

WORKS

ON2 -

GRAD_STUDENT DEPARTMENT

SSN

SUPERVISES

1 N

M

N

N

SSN PName Age Rank

Specialty

WORKS_ON

M

PNo SDate

Sponsor EDate

Budget

SName Age

Degree

ADVISES

N

senior

Graduate

MAJOR

N

DNo

DName Office

RUNS WORKS_IN

M

N Time

M

N