From: "William H. Hsu" <bhsu@CIS.KSU.EDU>
Date: March 2, 2008 10:44:43 PM CST
To: CIS560TA-L@LISTSERV.KSU.EDU
Subject: [CIS560TA-L] [PS1] Homework 1 - model solutions, Problems 1-3
Reply-To: "William H. Hsu" <bhsu@CIS.KSU.EDU>

Class,

Here are model solutions for PS1-1 through PS1-3.

1. Problem 1.6, p. 32 Silberschatz et al. 5e.  List four significant differences between a file-processing system and a DBMS.

a) Relational data representation.  The first class data types in a file-processing system may be characters, numbers, or strings, while those in a DBMS are relations.  Abstract data types must be defined for tuples, and to interpret relations as unordered sets of tubles.

b) Transparent I/O.  "Loading and saving" files involves scanning and parsing them or serializing them based on the specified abstract data type.  A DBMS uses schema definitions to help automate this.

c) Data manipulation.  File-based systems require specialized programs to perform the basic operations of a DBMS.  For example, Unix grep, cut, join can simulate some of the analogous operations to relational select, project, and join; sort, uniq, etc.  These are sometimes written case-by-case, even when we are using programming languages and scripting languages such as Perl and Python that have string and hash processing.

d) Indexing.  A DBMS has storage management schemes such as indexing for tertiary (sequential, esp. tape) versus secondary (esp. disk) storage. The DBMS programmer has more control over the way in which data is retrieved and stored relative to physical storage; while a file system may be configured and tuned for performance, the storage scheme is usually fixed.

2. Problem 1.9, p. 32 Silberschatz et al. 5e.  List at least two reasons why database systems support data manipulation using a declarative query language such as SQL, instead of just providing a library of C or C++ functions to carry out data manipulation.

a) Representation independence.  A query language such as SQL supports stronger typing in that the representation is not exposed as in C or even C++.  Not being able to improperly access data (e.g., by aliasing) or misinterpret it (by casting) means it is harder to violate the integrity of relations.  This follows the principle that "well-typed programs don't go wrong."

b) Standardization of operators.  SQL operators correspond to relational algebraic operators, which in turn correspond to set operators over relations (sets of tuples).

c) Compact expressions.  The language of expressions can be interpreted and expressions can be assigned, nested, substituted, etc.

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

a) what fields are coupled (i.e., functionally dependent)

We can think of the first and last columns as a unique key for the whole table.  Formally (customer_id, account_number) is a candidate key (minimal superkey) and the following functional dependency holds:

customer_id, account_number -> customer' [the whole table]

b) what records need to be updated when these fields are changed

There can be multiple customers per account and vice versa:

	- 192-83-7465 has two accounts
	- A-201 has two holders

Formally, this is an m-by-n or many-to-many relation.  When a customer moves OR an account number changes, all the rows corresponding to it have to change.  There should just be one row of Customer changed when something dependent on the ID number changes; similarly, there should just be one row of Account that changes with the account number.

c) how this redundancy could be alleviated

Splitting customer' into:

Customer (CUSTOMER_ID, customer_name, customer_street, customer_city)
Holds (CUSTOMER_ID, ACCOUNT_NUMBER)
Account (ACCOUNT_NUMBER, [other information])

will fix this particular redundancy.  This is a case of putting something into SECOND NORMAL FORM (2NF).  Note that Holds is a relationship set with two foreign keys (one for the primary key of each participating entity set).

-- 
====================================================
William H. Hsu, Ph.D.         bhsu-AT-cis.ksu.edu
Associate Professor of CIS, Kansas State University
Director, Lab for Knowledge Discovery in Databases
http://www.kddresearch.org     Google Talk: banazir
ICQ: 191317559     AIM/YIM/NSN: rizanabsith / hsuwh
====================================================