Essay on "Database Technology and Database Administration"

Quoted Instructions for "Database Technology and Database Administration" Assignment:

PART ONE OF THE ASSIGNMENT---------------------------------------------------------------------------------------------------------------------

MODULE 2 CASE ASSIGNMENT

Community library

You recently get a job offer from a local community 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 complete design for the community 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 commands 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 ***** 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 jwayne@hotmail.com Mountain View

0000002 John Smith (714)-110-8901 jsmith@yahoo.com Long Beach

0000003 Mary ***** (562)-456-3490 m*****@gmail.com 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 jwayne@hotmail.com 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 combination 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 combining 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.com/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.com/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 computer. 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.com/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.com/developerworks/wikis/display/DB2/FREE+Book-+Getting+Started+with+DB2+Express-C?S_TACT=index&S_CMP=expcsite

SQL tutorial:

http://www.1keydata.com/sql/sql.html

ODBMs v. RDBMs

http://www.objectivity.com/pages/objectivity/faq.asp

Object ***** databases management systems:

http://www.cs.cmu.edu/afs/cs.cmu.edu/user/clamen/OODBMS/README.html

About object ***** database management systems:

http://searchoracle.techtarget.com/definition/object-*****-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 accomplish 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 408-452-6241 john@hotmail.com 1100 Pine Sacramento CA

0002 Mary 714-452-0211 mary&yahoo.com 45 Rose San Diego CA

0003 ***** 562-890-2233 *****@gmail.com 6780 Winchester San Jose CA

0005 Lily 650-123-8000 lily@sbcglobal.com 2340 El Camino Albany KY

0006 Matt 312-567-4560 matt@att.com 211 Campbell Bowling green OH

0007 ***** 650-338-9910 *****@hotmail.com 5122 Peach Jackson MS

0008 ***** 310-456-6790 *****@yahoo.com 4590 Moody Occasion side CA

TWO ADDITIONAL RECORDS :

0009 Tim 770-323-9955 tim@gmail.com 5569 Decatur, Ga

0010 Sue 770-808-8688 sue@live.com 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.com/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 accomplish 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 decomposing. Read Normal forms, functional dependencies, decompositions 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 command 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, decompositions

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.com/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.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Basics of database normalization

http://support.microsoft.com/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.com/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.com/sql/database-administration/ten-common-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 community 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 comma.

To see data from two or more tables, we need to combine them into one table by using the join operation. These two tables must share one common 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 command 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.com/SQl/default.asp

SQL course

http://www.sqlcourse.com/index.html

Greenspun, P., SQL for Web Nerds: Queries

http://philip.greenspun.com/sql/queries.html

Greenspun, P., SQL for Web Nerds: Complex Queries

http://philip.greenspun.com/sql/complex-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

*****