Cis 560 Database System Concepts Spring 2007 Homework 1 of 10: Problem Set (PS1)



Download 32.68 Kb.
Date conversion08.05.2017
Size32.68 Kb.
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.


  1. (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.




  1. (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


  1. what fields are coupled

  2. what records need to be updated when these fields are changed

  1. 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.


    1. 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


    1. 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


  1. (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


  1. (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


  1. (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:




    1. Find the names of all employees who work for First Bank Corporation.

    1. 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.




  1. (10%) Relational Algebra: Updates. Problem 2.7, parts b, c.




      1. 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.

      2. Delete all tuples in the works relation for employees of Small Bank Corporation.




  1. (10%) Relations. Give an example of a:




  1. One-to-one function between a subset of A and a subset of B that is not onto.

  2. Onto function between a subset of A and a subset of B that is not one-to-one.




  1. (10%) Databases and Data Structures. Consider a table in a relational database.




    1. 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.

    2. 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.


The database is protected by copyright ©ininet.org 2016
send message

    Main page