Essay Instructions: PART ONE OF THE ASSIGNMENT---------------------------------------------------------------------------------------------------------------------
MODULE 2 CASE ASSIGNMENT
Community library
You recently get a job offer from a local munity library. The manager asks you to develop a database system to track customers and books they check out.
Task 1
Your initial assignment is to create a table for customers. The manager tells you that this table needs to track the following information: all customers' social security number, first and last name, address, city, state and zip code, email, phone number, birth date, and date of applying for a library card. Your task is to first "sketch" a customer table (basically what data items or columns should be included in the table) using the Customer_information table at background section as an example. Your table should contain the following:
Column heading include all the attributes that need to be in this table.
Populate the table with five records of data
Task 2
Your next task is to design one more table: Customer checkout table to track the books and CDs that the customer has checked out from the library. Customers and Customer Checkout table are related to one another.
The Customer table contains the basic data on each customer and the primary key is customer number. There is one row for each customer.
The Customer Checkout table contains the data for each book. The primary key consists of two pieces of data: Checkout number and customer number. One customer can check out 1 or more books/CDs at a time, but only one customer on a check out order.
Use the templates below to design the two tables.
"Templates" for Table Design
Customer Table
THE TEMPLATE SHOWN WAS JUST A TABLE THAT CONSIST OF 6 COLUMNS AND 4 ROWS.
Task 3
Draw an ER diagram of your plete design for the munity library. You can use Visio, or Microsoft word feature to draw the chart, or write down the table schemata and describe the relationships among them.
Include half page report about your experience with this assignment.
Case assignment expectations
demonstrate understanding of using ER diagram to represent database design.
Correctly design tables based on requirement.
BACKGROUND INFORMATION:
So what is a database anyway? A database is a structure that holds organized data (raw facts) and information (data that are processed to answer questions) for storage, access, update, and manipulation. Very often, people confuse database with database management systems (DBMS), DBMS is software that we use to create and manipulate databases, e.g. DB2 which is used for this course, Access, and Oracle. The relationship between DBMS and Databases is similar to the homework file you create using Microsoft Office.
Compared to using flat files such as Microsoft Excel, databases offer many advantages:
it holds much more data
since the data and the applications that manipulate data is separate, it is easier for databases to keep data integrity
it can be accessed by multiple users at the same time (this claim needs to be modified now multiple users can access excel simultaneously using skydrive although it is not quite user friendly)
efficient operations through performance optimization.
There are many types of databases based on the manner that the data is stored, organized, and manipulated:
Hierarchical model
Network model
Relational model
Entity-relationship
Object-relational model
Object model
Modern DBMSs are quite user friendly, it is easy to build a database quickly without giving too much consideration about database design, that creates redundancies and anomaly later. Therefore, we strongly suggest you to resist this temptation, solid relational database design takes time and patience to learn. This course will cover database design methodology in module 3. The methodology of designing relational databases can be summarized into the following steps:
Determine the scope of the project and identify all relevant Entities and Relationships (module 1 and 2)
Use Entity Relationship diagram to capture these entities and relationship. (module 2)
Convert the ER model to a number of relations. (module 2 and 3
Go through a process called normalization to eliminate or reduce redundancy by splitting relations.(module 4)
Database design is a very important processing before you start creating databases. After we finish database design, we can use SQL (Structured Query Language) to create, populate, and manipulate databases.
For example, if the store is having promotion event in Long beach, use the following SQL statement to retrieve the customers' names who lives in Long beach:
SELECT Name
FROM Customer
WHERE city=’Long Beach’;
More details on SQL mands are introduced in Module 4.
As we mentioned earlier, there are many kinds of RDBMS(relational database management systems). The one used in this course is IBM DB2 Express-C since it is popular and it is free.
Data modeling
Data modeling is very important for database design. How the data is modeled will determine how the data will be accessed and manipulated. In module 1, we learned that there are various kinds of databases based on how data is modeled, e.g. hierarchical model, network model, relational data model, object-relational model, and object oriented model. The most popular data modeling is relational data model which is also the focus of this course. In a relational database design, generally speaking, the process is to:
Decide on the purpose of this database and scope of the project
agree upon the kinds of information you would like to retrieve from the database
identify all relevant entities and relationships
Note the difference between entities and attributes. For example, student is an entity (can also be called a table, or a relation), name and phone number are two of the attributes associated with the student entity.
Use ER diagram to describe the identified entities and relations
Convert the ER model to a number of relation schemas
Eliminate (or reduce) redundancy by splitting relations. This process is called normalization
Each relation (also called table in relational database modeling jargon) contains a collection of values associated with these attributes. For example, in module 1, we have the following Customer Information table.
Customer_information
Customer ID Name Phone number Email City
0000001 John Wayne (650)-718-9920 Mountain View
0000002 John Smith (714)-110-8901 Long Beach
0000003 Mary Jane (562)-456-3490 Los Alamitos
In this module, we are going to use this table to introduce concepts that are important to database.
Tuple (record): Each table has a set of tuples which are ordered list of values. In this case, one tuple is
0000001 John Wayne (650)-718-9920 mountain view
Attributes: Each table has a set of attributes that describe this table. For example in this case, Customer_ID, Name, Phone number, Email, and City are the attributes for Customer_information table. Each attribute has different data types such as varchar, float, etc. All attributes must have unique names in the table.
Primary key: Each table has an attribute or bination of attributes that could unique identify one record from the others. For example: Customer_ID can distinguish one student from others even if they have the same name.
Database design issues
Pay attention to the following when you design a database
Each field should have one discrete data. For example, it is better to have separate fields for street number, street name, city, state, and zip code than bining them into one field. Otherwise, it will be hard to retrieve one piece of data such as city when it is mixed with other data.
No missing value is allowed in primary key.
There should be no orphan tables in the database. Each table should be related to one or more than one table. When two tables are supposed to be related, there must be a field that relates the two databases.
There is no need for each table to connect to every other table in the database.
When two tables are in a one- to-many relationship, the primary key from the "one" table should be inserted as a foreign key in the "many" table, but not the other way around.
All field names must be unique in each table. However, DBMS allows same names in different tables. However, it is good practice to create unique names for all tables to make it easier to write query.
Make field name as descriptive as possible. Field1 and Field2 do not make much sense while writing queries.
Set up build in Constraints to ensure data are entered correctly. For example: phone number and zip codes should fit in specified format.
Referential integrity needs to be enforced so that there are no orphans in the table. When two table are in relationship, their data need to be checked when they are created or deleted.
HOW TO ACCESS:
Download IBM DB2 Express-C
There are a wide range of database management systems to choose from. The one that we are going to use for this course is IBM DB2 Express - C. The reason this DBMS is chosen is that, it has provided a lot of functions and also it is free.
To download DB2 Express - C, you will need to go to http://www-01.ibm./software/data/db2/express/about.html, register with IBM first before download. If you have any questions, read the e-book Getting Started with DB2 Express-C) and videos. The ebook and video will help you with “the DB2 book” with the download and get familiar with the DB2 environment.
Follow the steps below for download:
Register with IBM
go to http://www-01.ibm./software/data/db2/express/about.html
click on Downloads link on the left of the site
Choose the suitable one based on your operating system. The current version is 9.7.4.
download a zip file to your puter. Unzip it
go to yourdir/EXPC/image/, and run the right setup application.
Read Chapter 3 of the DB2 instruction book for installation.
For this course, we will use the Control Center for database administration. For the advanced Database Management course, we will migrate to IBM Data Studio as the primary tool for database administration. Chapter 5 Sections 5.2 ??" 5.4 of the DB2 book describes some tools you may find useful for this course.
The goal is to ensure you have properly installed DB2 Express-C and get familiar with its environment. After you have finished downloading DB2 Express - C, Please follow the detailed installation instructions in Chapter 3 in IBM's DB2 book
REQUIRED READINGS TO COMPLETE THIS ASSIGNMENT:
Data, information, knowledge and their interrelationships:
http://www.tlainc./articl134.htm
Data base concepts:
http://www.fhi.rcsed.ac.uk/rbeaumont/virtualclassroom/chap7/s2/dbcon1.pdf
Chong, R, Hakes, I, and Ahuja, R., Getting Started with DB2 Express-C.
Chapter 1 ??" What is DB2 Express-C?; Chapter 3 ??" DB2 Installation.
http://www.ibm./developerworks/wikis/display/DB2/FREE+Book-+Getting+Started+with+DB2+Express-C?S_TACT=index&S_CMP=expcsite
SQL tutorial:
http://www.1keydata./sql/sql.html
ODBMs v. RDBMs
http://www.objectivity./pages/objectivity/faq.asp
Object oriented databases management systems:
http://www.cs.cmu.edu/afs/cs.cmu.edu/user/clamen/OODBMS/README.html
About object oriented database management systems:
http://searchoracle.techtarget./definition/object-oriented-database-management-system
Read relational data modeling section of Data modeling
http://www.liberty.edu/media/1414/%5B6330%5DERDDataModeling.pdf
ER diagram
http://www.sis.pitt.edu/~valeriab/1022-spring08/Chapter6.pdf
Relational Database Design Requirements
http://www.databasedev.co.uk/database_design_requirements.html
Allen, S. and Terry, E. (2005), Beginning Relational Data Modeling, Chapter 3 ??" Understanding Relational Modeling Terminology
http://cdad.tuiu.edu/Uploads/Presentations/59908Chapter3.pdf
PART 2 OF THE ASSIGNMENT-------------------------------------------------------------------------------------------------------------------------
MODULE 2 SLP
Developing a database system for A University Student
In this assignment, you will start to create a database using the DB2 Express-C Control Center without writing any SQL statement. You can find how to do it at 5.2 of the IBM DB2 book.
Project description
You are hired by University IT department. Your first assignment is to work on a database project to store student information and track their progress in their coursework (what courses students have taken and their scores etc). Eventually your database should be able to generate the following report:
Students who live in California
Student who live outside California
Name of students who take ITM440 course
Name of students who live in California and take ITM440 course
Student names, courses they have taken, and letter grade for each of these courses.
Any other information you would like to retrieve from the database?
By module 4, your database should acplish all the tasks above. In this assignment however, you are required to work on part of it.
SLP assignment
1) Your task is to first "sketch" a Student Information table (basically what columns should be included in the table) using the Student_information table at the following as example. Your table should contain the following:
Column heading include all the attributes that need to be in this table.
Populate the table with 10 records of data
You can use the Student_information table as example, add or remove attributes based on your analysis.
Student_information
Student ID Name Phone Email Street number Street name City State
0001 John 1100 Pine Sacramento CA
0002 Mary mary&yahoo. 45 Rose San Diego CA
0003 Jason 6780 Winchester San Jose CA
0005 Lily 2340 El Camino Albany KY
0006 Matt 211 Campbell Bowling green OH
0007 David 5122 Peach Jackson MS
0008 Jason 4590 Moody Occasion side CA
TWO ADDITIONAL RECORDS :
0009 Tim 5569 Decatur, Ga
0010 Sue 227 Dacula, Ga
2) Sketch a course information table using the following table as example. Feel free to add or remove attributes based on your analysis. Your table should contain the following:
Column heading include all the attributes that need to be in this table.
Populate the table with 10 records of data. (tip: use course catalogue for more course numbers and titles)
Course_information
Course ID Title credit
ITM432 Principles of Finance and Financial Information Systems 4
ITM433 Computer-Human Interaction, Groupware, and Usability 4
ITM434 Business Ethics and Social Issues in Computing 4
ITM435 Marketing and Marketing Information Systems 4
ITM436 Operations Management and Operations Information Systems 4
ITM440 Database Technology and Database Administration 4
ITM441 Network Technology and Network Administration 4
ADDITIONAL ITEMS TO ADD:
ITM423 ??" Systems Acquisition, Systems Development, and Project Management 4 Credits
ITM424 ??" Introduction to Software and Technical Support 4 Credits
ITM442 ??" Knowledge Management, Business Intelligence,and Enterprise Systems 4 Credits
3) Use ER diagram to represent the data modeling.
4) Write a one page discussion on problems you have encountered in this assignment and what issues you might find in this design.
5) Create table you have just designed in DB2 without using SQL statement. Take a screen shot of the tables and paste them to the document.
SLP assignment expectations
You are evaluated based on whether the tables have been created correctly and your demonstrated effort.
Clear explanation of your express with this assignment.
BACKGROUND INFORMATION:
YOU WILL USE THE BACKGROUND INFORMATION FROM PART ONE OF THE ASSIGNMENT PLUS
REQUIRED READINGS:
5.2 of the IBM DB2 book.
http://public.dhe.ibm./software/dw/db2/express-c/wiki/Getting_Started_with_DB2_Express_v9.7_p4.pdf
PART 3 OF THE ASSIGNMENT-------------------------------------------------------------------------------------------------------------------------
MODULE 3 CASE ASSIGNMENT
In this assignment, you will first normalize the tables in Community library database, then create the database using DB2. You are required to acplish the following
Based on what you have learned in this module, describe the tables you designed in module 2 case assignment, do you think they have met the requirement for the first, second, and third normal form? If you believe your tables are already in the third normal form, explain why.
If there are changes in your design, draw another ER diagram based on the revised design.
Write SQL statement to create tables you just revised in DB2 environment, and execute them in the Control Center. Take a screen shot of the SQL statements and tables you have created and paste it to word document.
Describe your experience with this assignment, problems you have encountered, and lessons learned.
Case assignment expectations
Demonstrate understanding on normalization
Apply SQL to create tables
ADDITIONAL INFORMATION NEEDED:
REFER BACK TO THE WORK DONE IN THE PREVIOUS SECTIONS AND INCLUDE THIS INFO
A well designed database will be free from anomalies. However, these anomalies do exist if we are not careful. The two main categories of anomalies are: data redundancy and violation of data dependency.
Data redundancy occurs when:
the same data is repeated in different tuples
the same data is stored in more than one relation (table)
Violation of data dependency occurs when:
information stored in the same table can not uniquely determine other information stored in the same table.
These anomalies can create many problems such as
using more memory than is necessary
update anomalies. For example, when changing the telephone number, instead of changaing one time in one table, you will have to change in several places, otherwise, the information is not accurate.
deletion anomalies. For example: when one customer has cancelled his account, all sales records related to him were removed, this will lead to sales data inconsistency.
Normalization is a process that helps to design database schemata that are free from anomalies. For example, the following relation schema
Customer (Customer_id, first_name, last_name, product_id, product_description)
can be split into three to avoid repeating first_name, last_name and class_name, like this:
Customer (Customer_id, first_name, last_name)
Product (Product_id, Product_description)
Sales (Customer_id, Product_id)
This normalization process is called deposing. Read Normal forms, functional dependencies, depositions for more details.
A normal form is a criterion on a relation schema. Study required reading materials for more information on normal forms and normalization.
This module will introduce some basics of Structured Query Language (SQL), the language used to manage and manipulate databases. The second module will go more in depth on the topic.
To create a table in SQL:
CREATE TABLE table_name
(
column_title1 data_type,
column_title2 data_type,
column_title3 data_type,
....
)
For example:
CREATE TABLE Customer
(
customer_ID varchar,
customer_Fname varchar,
customer_Lname varchar,
....
)
Please note that, it is okay to use lower case for key words such as CREATE TABLE, yet it helps to use upper case to distinguish mand from variable name. Also, indentation in SQL statement is for the purpose of easy reading, functionally, the following instruction achieves the same effect.
CREATE TABLE Customer (customer_ID varchar, customer_Fname varchar, customer_Lname varchar, ....)
In SQL, all attributes must be associated with a data type, and the data types available depend on the particular DBMS. Below are some widely available data types:
VARCHAR(x): text strings of at most x characters
INT: integers
FLOAT: real numbers
DATE: Gregorian dates
To delete the table you just create,
DROP TABLE Customer;
REQUIRED READING:
Normal form definitions
http://www2.yk.psu.edu/~lxn/IST_210/normal_form_definitions.html
Normal forms, functional dependencies, depositions
http://www.cs.utoronto.ca/~ryanjohn/teaching/cscc43-s11/c43-fd-v03.pdf
Normalization and SQL DDL Statements POWERPOINT WILL BE UPLOADED TO THE ORDER.
Allen, S. and Terry, E. (2005), Beginning Relational Data Modeling. Chapter 2 ??" Introducing Relational Theory (read the remaining sections from Introducing Normalization).
http://books.google./books?id=62CFtFea0NsC&printsec=frontcover&source=gbs_v2_summary_r&
cad=0#v=onepage&q&f=true
Litwin, P. Fundamentals of Relational Database Design
http://www.deeptraining./litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx
Basics of database normalization
http://support.microsoft./kb/283878
Andrew Cumming (2009). A Gentle Introduction to SQL
http://sqlzoo.net/
Chong, R, Hakes, I, and Ahuja, R., Getting Started with DB2 Express-C. Chapter 8 ??" Working with Database Objects.
http://www.ibm./developerworks/wikis/display/DB2/FREE+Book-+Getting+Started+with+DB2+Express-C?S_TACT=index&S_CMP=expcsite
Davidson, Louis (2007) Top Ten Common Database Design Mistakes
http://www.simple-talk./sql/database-administration/ten-mon-database-design-mistakes/
PART 4 OF THE ASSIGNMENT------------------------------------------------------------------------------------------------------------------------
MODULE 3 SLP
In this part of the assignment, you are to use what you have learned about normalization to improve the Student database design. Following are your tasks:
Normalize the tables you have designed in module 2. Explain how you redesign your tables to satisfy the requirements of 1NF, 2NF and 3NF. When necessary, it is perfectly okay to create another table. If you believe your tables designed in module 2 already met the normalization requirement, explain why.
Modify the database you created in Module 3: drop the tables that you have redesigned for normalization, and keep those that are intact.
Create new tables as required in your new design, using SQL in DBMS.
Include the SQL statements and screenshots of your new tables in the paper.
SLP assignment expectations
Create tables that met normalization standards.
Use SQL to create and drop tables.
ADDITIONAL INFORMATION:
USE ALL OF THE ABOVE INFORMATION TO COMPLETE THIS ASSIGNMENT
PART 5 OF THE ASSIGNMENT--------------------------------------------------------------------------------------------------------------------------
MODULE 4 CASE ASSIGNMENT
In this case assignment, you will write the SQL statement to work on the munity library database. Following are the tasks you are required to perform:
Populate data to the tables in your database
Retrieve the contact details of library customers
Retrieve the contact details of library customers who live in certain city
Update the residence information of a certain customer
Retrieve names and phone number of customers who have overdue books as of a certain date
Describe what you have learned in this assignment.
Write a paper (3 page) that includes your SQL statements and screenshots of the queries in DB2.
Case assignment expectations
Demonstrate the ability to work with SQL data manipulation language.
Communicate effectively with audience.
ADDITIONAL INFORMATION :
SQL DML (Data Management Language)
SELECT statement
Very often, we will want to retrieve data from one or more tables. To do this, we use SELECT statement. The format is the following:
SELECT columnName FROM tableName WHERE criteria
For example:
SELECT phone FROM Customer WHERE lastName = "Smith" and firstName = "John";
in this statement, we retrieve phone number of a customer called John Smith from Customer table. We could select multiple attributes and separate them with a ma.
To see data from two or more tables, we need to bine them into one table by using the join operation. These two tables must share one mon column. The format can be the following:
SELECT columnName, columnName2
FROM table1Name, table2Name
WHERE table1Name.column-name = table2Name.column-name2;
For example:
SELECT Customer_FirstName, Customer_LastName
FROM Customer, Order
WHERE Customer.Customer-id = Order.Cid;
This set of mand retrieves first name and last name of customers who have placed an order.
From the above example, you can see that the conditions that select certain data are placed behind WHERE. There are ways of forming conditional expressions
Comparison operators: =, <, <=, >, >=, <>.
Boolean operators: AND, OR, NOT
The LIKE operator used to find strings that match a given pattern
Parentheses can be used to indicate the order of evaluation.
The INSERT statement
The insert statement is used to add new values to a table. The general format is
INSERT INTO tableName VALUES (value1, value2...);
For example:
INSERT INTO Customer VALUES (00001, "John", "Smith");
This statement adds customer John Smith and his customer id to Customer table. When you add values to a table, the number of the values and their data types must be exactly the same as the attributes of the table, and in exactly that order.
The UPDATE statement
To modify values in a table, we use the following SQL statement format.
UPDATE tableName
SET attributeName = newValue or expression
WHERE criteria
For example:
UPDATE Customer
SET maritalStatus = 'm'
WHERE firstName = "John" and lastName = "smith";
The above statement updates the marital status of John Smith to m.
The DELETE statement
The delete statement removes rows in a table. The format is
DELETE FROM tableName
WHERE criteria
REQUIRED READINGS:
SQL DML Statements POWERPOINT WILL BE UPLOADED
SQL tutorial
http://www.w3schools./SQl/default.asp
SQL course
http://www.sqlcourse./index.html
Greenspun, P., SQL for Web Nerds: Queries
http://philip.greenspun./sql/queries.html
Greenspun, P., SQL for Web Nerds: Complex Queries
http://philip.greenspun./sql/plex-queries.html
PART 6 OF THE ASSIGNMENT-----------------------------------------------------------------------------------------------------------------------
MODULE 4 SLP
For the University Student database, generate reports that were specified in module 2 SLP.
Students who live in California
Student who live outside California
Name of students who take ITM440 course
Name of students who live in California and take ITM440 course
Student names, courses they have taken, and letter grade for each of these courses.
Any other information you would like to retrieve from the database?
Write a paper (2 page) that includes your SQL statements and screenshots of the result of your queries in DB2.
SLP assignment expectations
Demonstrate the ability to work with SQL data manipulation language.
ADDITIONAL INFORMATION:
USE ALL THE ABOVE INFORMATION, INCLUDING MODULE 2 SLP RESULTS AND THE DB2 SYSTEM
PLEASE SEPARATE ALL PAPERS BY SECTIONS. .
3 PAGES FOR MODULE 2 CASE ASSIGNMENT
2 PAGES FOR MODULE 2 SLP
3 PAGES FOR MODULE 3 CASE ASSIGNMENT
2 PAGES FOR MODULE 3 SLP
3 PAGES FOR MODULE 4 CASE ASSIGNMENT
2 PAGES FOR MODULE 4 SLP
There are faxes for this order.
