| CIS 560 Database System Concepts
Spring 2007
Homework 1 of 10: Problem Set (PS1)
Warm-up: Basics, Relations and Relational Algebra
Assigned: Sat 13 Jan 2007
Due: Fri 26 Jan 2006 (before midnight)
The purpose of this assignment is to exercise your basic understanding of relations and sets, help you apply these concepts to develop expressions in basic relational algebra, and illustrate how these expressions represent queries.
This homework assignment is worth a total of 20 points (100%). Points are rounded to the nearest 5%.
Upload an electronic copy of the assignment in PDF form (converted from your word processor, or scanned) to your K-State Online (KSOL) drop box before the due date and time.
-
(10%) Database Management Systems (DBMS). Problem 1.6, p. 32 Silberschatz et al. 5e. List four significant differences between a file-processing system and a DBMS.
-
(20%) Database Design Intro. Adapted from Problem 1.10, p. 32 Silberschatz et al. 5e. Explain what problems are caused by the design of the following table, customer’:
Hint: See Section 1.6.4 on normalization. Discuss redundancies and give examples of
-
what fields are coupled
-
what records need to be updated when these fields are changed
-
how this redundancy could be alleviated
In Chapter 6 on Entity-Relational Data Models, we will see how the right schema design reveals these redundancies, and in Chapter 7 on Relational Database Design, we will see how normalization provides formal definitions of dependency and the elimination of “bad” dependencies.
-
customer_id
|
customer_name
|
customer_street
|
customer_city
|
account_number
|
192-83-7465
|
Johnson
|
12 Alma St.
|
Palo Alto
|
A-101
|
192-83-7465
|
Johnson
|
12 Alma St.
|
Palo Alto
|
A-201
|
677-89-9011
|
Hayes
|
3 Main St.
|
Harrison
|
A-102
|
182-73-6091
|
Turner
|
123 Putnam St.
|
Stamford
|
A-305
|
321-12-3123
|
Jones
|
100 Main St.
|
Harrison
|
A-217
|
336-66-9999
|
Lindsay
|
175 Park Ave.
|
Pittsfield
|
A-222
|
019-28-2746
|
Smith
|
72 North St.
|
Rye
|
A-201
|
For problems 3-4, consider the two project options for this semester. Choose one for your solution (this need not be your final project choice). Indicate which domain you are discussing.
-
Angband Monster Memory Database (AMMDB): Maintaining a server-side coalesced database (with views combined across multiple players) of the monster memory in the computer role-playing game Angband.
Example:
http://www.rit.edu/~djl5698/angband/images/AMPFI/ampfi491-recall.JPG
Original content:
http://www.thangorodrim.net/spoilers/monsters_short.html
-
GradMiner: Preparing a university admissions and grade database for data mining to identify strong predictors of academic probation and dismissal.
Data modeling
http://www.byte.com/art/9706/sec7/art3.htm
Example object-relational (O-R) diagram:
http://www.w3.org/Conferences/WWW4/Papers2/282/#odl
-
(10%) Practical Databases. What do you think is a good server-side programming programming platform for the project, and why? That is, what would you use as the server-side programming language and database application programmer interface (DB API) mechanism?
http://en.wikipedia.org/wiki/Server-side_scripting
http://en.wikipedia.org/wiki/Application_programming_interface
http://en.wikipedia.org/wiki/Open_Database_Connectivity
http://en.wikipedia.org/wiki/Java_Database_Connectivity
-
(20%) Query Example. Give an example in English of a real select query that a user might submit over a web form, and write it in relational algebra.
For problems 7-8, refer to Sections 2.2 – 2.3 and consider the following relational database, where the primary keys are underlined:
employee (person_name, street, city)
works (person_name, company_name, salary)
company (company_name, city)
manages (person_name, manager_name)
Also refer to the following references.
Databases from scratch, intro:
http://www.geekgirls.com/databases_from_scratch_1.htm
Databases from scratch, design basics:
http://www.geekgirls.com/databases_from_scratch_2.htm
-
(10%) Relational Algebra: Queries. Problem 2.5, parts a, c. Consider the relational database above, where the primary keys are underlined. Give an expression in the relational algebra to express each of the following queries:
-
Find the names of all employees who work for First Bank Corporation.
-
Find the names, street address, and cities of residence of all employees who work for First Bank Corporation and earn more than $10,000 per annum.
-
(10%) Relational Algebra: Updates. Problem 2.7, parts b, c.
-
Give all managers in this database a 10 percent salary raise, unless the salary would be greater than $100,000. In such cases, give only a 3 percent raise.
-
Delete all tuples in the works relation for employees of Small Bank Corporation.
-
(10%) Relations. Give an example of a:
-
One-to-one function between a subset of A and a subset of B that is not onto.
-
Onto function between a subset of A and a subset of B that is not one-to-one.
-
(10%) Databases and Data Structures. Consider a table in a relational database.
-
What kind of C++ or Java data structure would you use to represent it, and why? Describe the data type (especially its dimension) and explain how memory for table entries is allocated.
-
For relational join operations, what is the advantage of maintaining records in sorted order by some key?
Extra credit (10%): Look at Figure 1.3, the sample E-R diagram of customer’ from p. 19 of Silberschatz et al. 5e and PS1-2 above. What is a primary key of depositor, and why? How many unique elements of customer, account, and depositor are there for the above example?
Class Participation (required):
Select a term project topic by Fri 26 Jan 2007. Post any questions you wish in the class mailing list CIS560-L@listserv.ksu.edu before or after your choice, and state your choice by e-mail to CIS560TA-L@listserv.ksu.edu.
Share with your friends: |
