This paper reveals the technique to develop a database in the DB2 environment. With DB2 software, the paper creates database for local community library to track the following information: • Customer first name and last name • City, state and zip code • Customer Social Security Number • Customer email, phone number and birth date • Date of applying for a library card. The paper also develops a database for students to generate the following report: • Students living in California • Student living outside California • Name of students taking ITM440 course • Name of students living in California and take ITM440 course • Student names, courses taken, and letter grade for each course.
Database Technology Administration
Database Technology and Database Administration
Scenario
The local community library intends to develop a database system to track customers and books that customers will like to checkout. As a new employee of local community library, I am asked to assist the library to track the following information:
Customer Social Security Number
Customer email, phone number and birth date
Date of applying for a library card.
The paper provides the sketch of a customer information table to provide greater understanding on the data to be included in the table.
Customer Information Table
This section provides a sketch of customer table that displays column heading and all the attributes of the table. The table contains five records.
Customer Information Table
Customer_ID
First_Name
Last_ Name
City
State
Zip_Code
Social_ Security_ Number
Phone_ Number
Library_ Card_ Date
John
Wayne
Muntain View
NY
60640
11111
(650) [HIDDEN]
12/02/2010
John
Smith
Long Beach
Il
60641
11112
(714) [HIDDEN]
15/05/2010
Mary
Jane
Los Alamitos
AL
60642
11113
(562) [HIDDEN]
14/05/2011
Anthony
Philip
Chicago
IL
60643
11114
(800) [HIDDEN]
13/04/2001
Higgins
Sandler
New York City
NY
60644
11115
(800)-2589-111
28/09/2011
Task 2: Customer Table
This section designs more tables that reveal customer checkout that track the CDs and the books that the customer has checked from the library. The tables reveal the relationship between customer and customer checkout. The data of each book is revealed in the Customer Checkout table, and the primary key consists of two pieces of data such as customer number and Checkout number. From the table, one customer can check out one or more CDs/books at a time. However, only one customer could check out order at a time.
Table revealing Customer Checkout
Task 3: Entity Relation Diagram for the Community Library
This section provides Entity Relation (ER) diagram for the complete design of the community library. To complete the ER diagram. The paper uses the Visio to draw the chart. The paper also writes down the table schema and the relationship among them. The paper follows Chen Model in presenting the ER diagram. (Orr,2007).
Fig 1: ER Diagram for the Community Library
Experience gained in the Assignment
The experience gained in this assignment is that the author has understood that a library database must contain the customer information, the physical location and the book and CD information. From the database designed, it is revealed the library can keep track of all books and CDs as well as their attributes, locations, status and late returns. The ISBN is used to identify each book ordered by the customers. To sign up for a library, a customer will have to provide his or her personal details such as names, address, email, phone number and date of birth, and a customer is assigned an ID as an identification method. At the checkout operation, a customer is required to present a library card. Moreover, the assignment has enhanced the greater understanding of the author about the method to use the ER diagram to relate the entities in the database. Relationship of the entities in the ER diagram assists the author to create tables.
Correct Table Design Based on Requirement
From the ER diagram, it is clear that the customers, cards, and CD correspond to entities in the ER model. From the ER diagram, the books and CDs are separated to allow database to store multiple items without redundancy. It is critical to eliminate redundancy in order to eliminate repeating group. In addition, the ER diagram has revealed the customer operation till the checkout operation.
NORMALIZATION FOR COMMUNITY LIBRARY
BOOK INFORMATION
ISBN
Address
City
State
CDs
CD ID
YEAR
PRICE
SHIP TO
Customer ID
Address
City
State
ORDERS
Quantity
ORDER CONFIRMATION
Customer ID
Item
Ship Date
Tracking Number
Invoice
PART 3 OF THE ASSIGNMENT
Scenario
The university intends to develop a database system to store student information and track the progress of students in their coursework. The database to be developed will generate the following report:
Students living in California
Student living outside California
Name of students taking ITM440 course
Name of students living in California and take ITM440 course
Student names, courses taken, and letter grade for each course.
Any other information likely to be retrieved from the database
1) Student Information Table
The section provides the student information table which contains student information such as Student ID, Name,
Street Number, Street Name, City, State, Email and Phone Number.
Student Information Table
Student_ID
Street Number
Street Name
City
State
Phone_ Number
John
Pine
Sacramento
CA
408-452-6241
Mary
45
Rose
San Diego
CA
mary&yahoo.com
714-452-0211
Jason
Winchester
San Jose
CA
562-890-2233
Bob
Santa
Cruz
CA
800-256-4587
Lily
El Camino
Albany
KY
650-123-8000
Matt
Campbell
Bowling green
OH
650-123-8000
David
Peach
Jackson
MS
650-338-9910
Jason
Moody
Occasion side
CA
310-456-6790
Tim
Decatur
Decatur
GA
770-323-9955
Sue
Dacula
Dacula
GA
770-808-8688
2) Course Information Table
This section provides course information table that contains course ID, course title and course credit.
Course Information Table
Course ID
Credit
Principles of Finance and Financial Information Systems
4
Computer-Human Interaction, Groupware, and Usability
4
Business Ethics and Social Issues in Computing
4
Marketing and Marketing Information Systems
4
Management and Operations Information Systems
4
Database Technology and Database Administration
4
Network Technology and Network Administration
4
Systems Acquisition, Systems Development, and Project Management
4
Introduction to Software and Technical Support
4
Knowledge Management, Business Intelligence, and Enterprise Systems
4
3) Use ER diagram to represent the data modeling.
This section provides ER diagram for the data modeling of student information that includes Student_ID, Name, Street Number, Street Name, City, State,
Email, and Phone Number. The ER diagram also reveals the Course ID, Course Tile, and Credit allotted to each course.
ER DIAGRAM FOR STUDENT INFORMATION
SCHOOL
PK
School_Name
PK 1
PK2
Schoo_Code
School_city
School_zip code
DEPARTMENT
PK
Dept_Code
PK 1
PK2
School_Code
Dept_Neame
Dept_Number
COURSE
PK
Course_ID
PK1
PK2
PK3
Course_Title
Course Credit
Grade
STUDENT
PK
Student_ID
PK1
PK2
Student _ Name
Stu_Street Number
Stu_ Street Name
Stu_ City Student_State
Student Email
Stu_Phone Number
PROFESSOR
PK
Employee_Number
PK1
PK2
Dept_Code
Prof_Speciality
Prof_Rank
ENROLL
PK
Enroll_Code
PK 1
PK2
Enroll_Date
Enroll_Number
4) The problem encountered in this assignment is about the ER diagram. Completing ER diagram accurately takes time and since ER diagram is very critical in the database design, the author spent great lot time in completing ER diagram accurately. Another challenge faced in this assignment is the problem of downloading the IBM DB2 Express. It is very cumbersome to download the IBM DB2 Express to complete the assignment. The IBM DB2 software contains large file, and it takes significant amount of time before downloading the entire file. The issue found in the design is the process of using DB2 only to create the database. The assignment ought to have allowed the students the option of choosing one of the major database software instead of specifying only DB2 to create the database. Moreover, BD2 Express is not so popular among students in creating database.
STUDENT INFORMATION TABLE
STUDENTID
STREETNUMBER
STREET NAME
CITY
STATE
PHONENUMBER
CHAR (4) NOT NULL
CHAR (25) NOT NULL
CHAR (10) NOT NULL
CHAR (25) NOT NULL
CHAR (25) NOT NULL
CHAR (25)
CHAR (25)
CHAR (15)
JOHN
PINE
SACRAMENTO
CA
401-452-6241
MARY
45
ROSE
SAN DIEGO
CA
714-452-0211
JASON
WINCHESTER
SAN JOSE
CA
562-890-2233
BOB
SANTA
CRUZ
CA
800-256-4587
LILY
EL CAMINO
ALBANY
KY
650-123-8000
MATT
CAMPBELL
BOWLING GREEN
OH
312-567-4650
DAVID
PEACH
JACKSON
MS
650-338-9910
JASON
MOODY
OCCASION SIDE
CA
310-456-6790
TIM
DECATUR
DECATUR
GA
770-323-9955
SUE
DACULA
DACULA
GA
770-808-8688
TABLE FOR COURSE INFORMATION
COURSEID
COURSETITLE
CREDIT
CHAR (10) NOT NULL
CHAR (50) NOT NULL
CHAR (1) NOT NULL
PRINCIPLES OF FINANCE AND FINANCIAL INFORMATION SYSTEMS
4
COMPUTER HUMAN INTERACTION, GROUPWARE, USABILITY
4
Business ETHICS AND SOCIAL ISSUES IN COMPUTING
4
MARKETING AND MARKETING INFORMATION SYSTEMS
4
Management AND OPERATION INFORMATION SYSTEMS
4
DATABASE TECHNOLOGY AND DATABASE ADMINISTRATION
4
NETWORK TECHNOLOGY AND NETWORK ADMINISTRATION
4
PART 3 OF THE ASSIGNMENT
This section provides normalization of Community library database. By using IBM DB2, the paper proceeds to create the database. Normalization is the process of identifying and correcting poor table structure. The goal of using normalization is to reduce redundancies in the database as well as eliminating data anomalies. Tossy (2008) argue "the process of breaking the first table into two tables is called normalization." Using this procedure, the paper breaks the Community library data into Third Normal (3NF) to decompose the relationship.
Customer Information Table
Customer_ID
First_Name
Last_ Name
City
State
Zip_Code
Social_ Security_ Number
Phone_ Number
Library_ Card_ Date
John
Wayne
Mountain View
NY
60640
11111
(650) [HIDDEN]
12/02/2010
John
Smith
Long Beach
Il
60641
11112
(714) [HIDDEN]
15/05/2010
Mary
Jane
Los Alamitos
AL
60642
11113
(562) [HIDDEN]
14/05/2011
Anthony
Philip
Chicago
IL
60643
11114
(800) [HIDDEN]
13/04/2001
Higgins
Sandler
New York City
NY
60644
11115
(800)-2589-111
28/09/2011
Book Information
Book ISBN
Author
Price
John
Wayne
$85
John
Smith
$70
Mary
Jane
$65
Anthony
Philip
$78
Higgins
Sandler
$80
CD Information
CD ID
Year
Price
John
2005
$100
John
2007
$90
Mary
2010
$105
Anthony
2011
$75
Higgins
2011
$104
SQL FOR STUDENT INFORMATION TABLE
CONNECT TO LIBRARY;
CREATE TABLE "USER."
"STUDENT INFORMATION TABLE" (
"STUDENT ID" CHARACTER (4) NOT NULL,
NAME CHARACTER (25) NOT NULL,
"STREET NUMBER" CHARACTER (10) NOT NULL,
"STREET NAME" CHARACTER (25) NOT NULL,
CITY CHARACTER (25) NOT NULL,
STATE CHARACTER (10) NOT NULL,
EMAIL CHARACTER (25) NOT NULL,
"PHONE NUMBER" CHARACTER (15) NOT NULL );
CONNECT RESET;
SQL FOR Book_Information
CONNECT TO LIBRARY;
CREATE TABLE "USER";
CREATE TABLE BookInformation (
"BookISBN" CHARACTER (10) NOT NULL;
"AUTHOR" CHARACTER (25) NOT NULL;
"TITLE" CHARACTER (50) NOT NULL;
"PRICE" CHARACTER (10) NOT NULL;
CONNECT RESET;
SQL STATEMENT FOR CD
CONNECT TO LIBRARY;
CREATE TABLE "USER";
CREATE TABLE CD (
"CD-Id" CHARACTER (25) NOT NULL;
"AUTHOR" CHARACTER (25) NOT NULL;
"TITLE" CHARACTER (25) NOT NULL;
"PRICE" CHARACTER (25) NOT NULL;
CONNECT RESET;
Experience Gained from this Assignment
The assignment has enhanced my greater understanding on the method to use IBM DB2 Express to develop database. Although, it takes me several hours to download the software; however; I have learned several techniques to develop the database. Although, I have already developed the method to create database from the classroom note, however, the IBM DB2 Express has improved my understanding on the method to use the software to develop a database table. In addition, the assignment has developed my knowledge on the database normalization. Through normalization process, I have been able to improve the functionality of the table. While this assignment has improved my knowledge on the database development, there are still some challenges faced in the assignment; first, it took me some time to write the SQL code to create the table. Essentially, thorough understanding of SQL is very important in the database, and understanding of SQL is very important in this assignment.
PART 4 OF THE ASSIGNMENT
Normalization is the process of simplifying database in the normal form to give the database optimal structure. The normal form should be in a linear progression of a rule where higher normal form achieves better and more efficient form. Normalization theory reveals normal forms as follows:
1. First Normal Form (INF)
2. Second Normal Form (2NF)
3. Third Normal Form (3NF)
4. Boyce Codd Normal Form
5. Fourth Normal Form
6. Fifth Normal Form
The student database has met First Normal Form (INF) normalization requirements based on the following reasons:
First Normal Form (INF) reveals that all column values in the database must be atomic. The INF dictates that for every row-by-column in a given table, there should be only one value and not list of value or array of value. The benefit of this rule is that if all the list of values is stored in a single form, there is no way to retrieve data from the database, and there is no way to manipulate those values. The Student Table does not violate INF rules because the lists of values are stored in a separate column. Typically, the data stored in the Student Table are atomic because it is easy to retrieve information from the table, and it is easy to create report from the table. While INF prohibits the presence of repetition, the Student Table satisfies the requirements of the INF because the table stores single item columns in separate column.
Second Normal Form (2NF)
A table is in Second Normal Form (2NF) if it has satisfied the requirement of INF and if its non-key column is fully dependent on the entire primary key. Similarly, "tables should only store data related to one entity and the entity should be described by its primary key. In addition a table is said to be in Third Normal Form (3NF), if it is in 2NF and if all non-key columns are mutually independent." (Litwin, Getz & Gunderloy, 2002 P. 13)." Based on the rules of the 2NF and 3NF, it is critical to redesign a new table to satisfy the Second Normal Form and Third Normal Form requirements. The new tables are created to meet normalization standard and the tables are created using SQL in DBMS.
SQL FOR STUDENT INFORMATION TABLE
CONNECT TO LIBRARY;
CREATE TABLE "USER."
"STUDENT INFORMATION TABLE" (
"STUDENT ID" CHARACTER (4) NOT NULL,
NAME CHARACTER (25) NOT NULL,
"STREET NUMBER" CHARACTER (10) NOT NULL,
"STREET NAME" CHARACTER (25) NOT NULL,
CITY CHARACTER (25) NOT NULL,
STATE CHARACTER (10) NOT NULL,
EMAIL CHARACTER (25) NOT NULL,
"PHONE NUMBER" CHARACTER (15) NOT NULL );
CONNECT RESET;
NEW STUDENT INFORMATION TABLE
STUDENTID
STREETNUMBER
STREET NAME
CITY
STATE
PHONENUMBER
CHAR (4) NOT NULL
CHAR (25) NOT NULL
CHAR (10) NOT NULL
CHAR (25) NOT NULL
CHAR (25) NOT NULL
CHAR (25)
CHAR (25)
CHAR (15)
JOHN
PINE
SACRAMENTO
CA
401-452-6241
MARY
45
ROSE
SAN DIEGO
CA
714-452-0211
JASON
WINCHESTER
SAN JOSE
CA
562-890-2233
BOB
SANTA
CRUZ
CA
800-256-4587
LILY
EL CAMINO
ALBANY
KY
650-123-8000
MATT
CAMPBELL
BOWLING GREEN
OH
312-567-4650
DAVID
PEACH
JACKSON
MS
650-338-9910
JASON
MOODY
OCCASION SIDE
CA
310-456-6790
TIM
DECATUR
DECATUR
GA
770-323-9955
SUE
DACULA
DACULA
GA
770-808-8688
SQL FOR COURSE INFORMATION
CONNECT TO LIBRARY;
CREATE TABLE "USER." "COURSE INFORMATION TABLE" (
"COURSE ID" CHARACTER (10) NOT NULL,
"COURSE TITLE" CHARACTER (50) NOT NULL,
CREDIT CHARACTER (1) NOT NULL );
CONNECT RESET;
NEW TABLE FOR COURSE INFORMATION
COURSEID
COURSETITLE
CREDIT
CHAR (10) NOT NULL
CHAR (50) NOT NULL
CHAR (1) NOT NULL
PRINCIPLES OF FINANCE AND FINANCIAL INFORMATION SYSTEMS
4
COMPUTER HUMAN INTERACTION, GROUPWARE, USABILITY
4
Business ETHICS AND SOCIAL ISSUES IN COMPUTING
4
MARKETING AND MARKETING INFORMATION SYSTEMS
4
Management AND OPERATION INFORMATION SYSTEMS
4
DATABASE TECHNOLOGY AND DATABASE ADMINISTRATION
4
NETWORK TECHNOLOGY AND NETWORK ADMINISTRATION
4
Thus, the above table has satisfied requirements of First Normal Form (1NF) and Second Normal Form (2NF). However, they have not yet satisfied the Third Normal Form (3NF). To ensure that all the tables meet the normalization process and satisfy the 3NF, the table must be mutually independent. (Johnson, 2011).
The paper provides the SQL statement and tables that meet 3NF
SQL STATEMENT OF STUDENTS WHO LIVE CALIFORNIA
CONNECT TO LIBRARY;
CREATE TABLE "USER."
"STUDENT WHO LIVE IN CALIFORNIA" (
"STUDENT ID" CHARACTER (4) NOT NULL,
NAME CHARACTER (25) NOT NULL,
"STREET NUMBER" CHARACTER (10) NOT NULL,
"STREET NAME" CHARACTER (25) NOT NULL,
CITY CHARACTER (25) NOT NULL,
STATE CHARACTER (10) NOT NULL,
EMAIL CHARACTER (25) NOT NULL,
"PHONE NUMBER" CHARACTER (15) NOT NULL );
CONNECT RESET;
TABLE OF STUDENTS WHO LIVE IN CALIFORNIA
STUDENTID
STREETNUMBER
STREET NAME
CITY
STATE
PHONENUMBER
CHAR (4) NOT NULL
CHAR (25) NOT NULL
CHAR (10) NOT NULL
CHAR (25) NOT NULL
CHAR (25) NOT NULL
CHAR (25)
CHAR (25)
CHAR (15)
JOHN
PINE
SACRAMENTO
CA
401-452-6241
MARY
45
ROSE
SAN DIEGO
CA
714-452-0211
JASON
WINCHESTER
SAN JOSE
CA
562-890-2233
BOB
SANTA
CRUZ
CA
800-256-4587
JASON
MOODY
OCCASION SIDE
CA
310-456-6790
SQL STATEMENT OF STUDENTS WHO LIVE OUTSIDE CALIFORNIA
CONNECT TO LIBRARY;
CREATE TABLE "USER."
"STUDENT WHO LIVE OUTSIDE CALIFORNIA" (
"STUDENT ID" CHARACTER (4) NOT NULL,
NAME CHARACTER (25) NOT NULL,
"STREET NUMBER" CHARACTER (10) NOT NULL,
"STREET NAME" CHARACTER (25) NOT NULL,
CITY CHARACTER (25) NOT NULL,
STATE CHARACTER (10) NOT NULL,
EMAIL CHARACTER (25) NOT NULL,
"PHONE NUMBER" CHARACTER (15) NOT NULL );
CONNECT RESET;
TABLE OF STUDENTS WHO LIVE OUTSIDE CALIFORNIA
STUDENTID
STREETNUMBER
STREET NAME
CITY
STATE
PHONENUMBER
CHAR (4) NOT NULL
CHAR (25) NOT NULL
CHAR (10) NOT NULL
CHAR (25) NOT NULL
CHAR (25) NOT NULL
CHAR (25)
CHAR (25)
CHAR (15)
LILY
EL CAMINO
ALBANY
KY
650-123-8000
MATT
CAMPBELL
BOWLING GREEN
OH
312-567-4650
DAVID
PEACH
JACKSON
MS
650-338-9910
TIM
DECATUR
DECATUR
GA
770-323-9955
SUE
DACULA
DACULA
GA
770-808-8688
SQL STATEMENT FOR STUDENTS WHO TAKE ITM440 COURSE
CONNECT TO LIBRARY;
CREATE TABLE "USER." "STUDENTS WHO TAKE IITM40 COURSE" (
"NAME" CHARACTER (25) NOT NULL,
"COURSE ID" CHARACTER (10) NOT NULL,
"COURSE TITLE" CHARACTER (50) NOT NULL,
CREDIT CHARACTER (1) NOT NULL );
CONNECT RESET;
NAME OF STUDENTS WHO TAKE ITM440 COURSE
COURSEID
COURSETITLE
CREDIT
CHAR (25) NOT NULL
CHAR (10) NOT NULL
CHAR (50) NOT NULL
CHAR (1) NOT NULL
JOHN
DATABASE TECHNOLOGY AND DATABASE ADMINISTRATION
4
MARY
DATABASE TECHNOLOGY AND DATABASE ADMINISTRATION
4
JASON
DATABASE TECHNOLOGY AND DATABASE ADMINISTRATION
4
BOB
DATABASE TECHNOLOGY AND DATABASE ADMINISTRATION
4
LILY
DATABASE TECHNOLOGY AND DATABASE ADMINISTRATION
4
MATT
DATABASE TECHNOLOGY AND DATABASE ADMINISTRATION
4
DAVID
DATABASE TECHNOLOGY AND DATABASE ADMINISTRATION
4
JASON
DATABASE TECHNOLOGY AND DATABASE ADMINISTRATION
4
TIM
DATABASE TECHNOLOGY AND DATABASE ADMINISTRATION
4
SUE
DATABASE TECHNOLOGY AND DATABASE ADMINISTRATION
4
SQL STATEMENT FOR STUDENT NAMES, COURSE TAKEN AND GRADES
CONNECT TO LIBRARY;
CREATE TABLE "USER." "STUDENT NAMES, COURSE TAKEN AND GRADES" (
"NAME" CHARACTER (25) NOT NULL,
"COURSE TITLE" CHARACTER (50) NOT NULL,
"GRADE" CHARACTER (1) NOT NULL );
CONNECT RESET;
TABLE FOR STUDENT NAMES, COURSE TAKEN AND GRADES
COURSETITLE
GRADE
CHAR (25) NOT NULL
CHAR (50) NOT NULL
CHAR (1) NOT NULL
JOHN
DATABASE TECHNOLOGY AND DATABASE ADMINNNNISTRATION
A
JOHN
Principles of Finance and Financial Information Systems
B
JOHN
Computer-Human Interaction, Groupware, and Usability
B
MARY
DATABASE TECHNOLOGY AND DATABASE ADMINISTRATION
A
MARY
Business Ethics and Social Issues in Computing
A
MARY
Marketing and Marketing Information Systems
B
JASON
DATABASE TECHNOLOGY AND DATABASE ADMINISTRATION
B
JASON
Management and Operations Information Systems
A
JASON
Computer-Human Interaction, Groupware, and Usability
A
BOB
DATABASE TECHNOLOGY AND DATABASE ADMINISTRATION
A
BOB
Network Technology and Network Administration
A
BOB
Management and Operations Information Systems
A
LILY
DATABASE TECHNOLOGY AND DATABASE ADMINISTRATION
B
LILY
Network Technology and Network Administration
A
LILY
Principles of Finance and Financial Information Systems
A
MATT
DATABASE TECHNOLOGY AND DATABASE ADMINISTRATION
B
MATT
Management and Operations Information Systems
A
MATT
Business Ethics and Social Issues in Computing 4
A
DAVID
DATABASE TECHNOLOGY AND DATABASE ADMINISTRATION
B
DAVID
Computer-Human Interaction, Groupware, and Usability
B
DAVID
Business Ethics and Social Issues in Computing 4
B
JASON
DATABASE TECHNOLOGY AND DATABASE ADMINISTRATION
A
JASON
Principles of Finance and Financial Information Systems
A
JASON
Network Technology and Network Administration
B
TIM
DATABASE TECHNOLOGY AND DATABASE ADMINISTRATION
A
TIM
Marketing and Marketing Information Systems
B
TIM
Business Ethics and Social Issues in Computing 4
A
SUE
DATABASE TECHNOLOGY AND DATABASE ADMINISTRATION
A
SUE
Management and Operations Information Systems
B
SUE
Network Technology and Network Administration
B
PART 5 OF THE ASSIGNMENT
This section provides the SQL statement to work on the community library database.
Below is the SQL statement of the community library database.
CONNECT TO LIBRARY;
CREATE TABLE "USER." "COMMUNITY LIBARY DATABASE" (
CUSTOMER_ID CHARACTER (10) NOT NULL,
FIRST_NAME CHARACTER (25) NOT NULL,
LAST_NAME CHARACTER (25) NOT NULL,
CITY CHARACTER (20) NOT NULL,
STATE CHARACTER (20) NOT NULL,
ZIP_CODE CHARACTER (10) NOT NULL,
SOCIAL_SECURITY_NUMBER CHARACTER (15) NOT NULL,
EMAIL CHARACTER (25) NOT NULL,
PHONE_NUMBER CHARACTER (15) NOT NULL,
LIBRARY_CARD_DATE DATE NOT NULL );
CONNECT RESET;
TABLE OF COMMUNITY LIBARY
CUSTOMERID
FIRSTNAME
LASTNAME
CITY
STATE
ZIPCODE
SOCIAL SECURITY NUMBER
LIBRARY
CARD
CHAR (10) NOT NULL
CHAR (25) NOT NULL
CHAR (25) NOT NULL
CHAR (20) NOT NULL
CHAR (2) NOT NULL
CHAR (10)
CHAR (15) NOT NULL
CHAR (25) NOT NULL
EMAIL CHAR (25) NOT NULL
JOHN
WAYNE
MOUNTAIN VIEW
NY
10004
(650) [HIDDEN]
12-10-2010
JOHN
SMITH
LONG BEACH
IL
60640
(714) [HIDDEN]
15-05-2011
MARY
JANE
LOS ALAMITOS
AL
36523
11111225
(562) [HIDDEN]
13-04-2010
HIGGINS
SANDLER
NEW YORK CITY
NY
10006
(800)-2589-111
28-08-2011
The following SQL statement is used to retrieve the contact details of the of library customers.
SELECT City, State, ZipCode, Email, PhoneNumber FROM Community Library Database;
The following SQL statement is used to retrieve the contact details of library customers who live in certain city.
SELECT City, State, ZipCode, Email, PhoneNumber FROM CommunityLibraryDatabase; WHERE City = 'MountainView';
The following SQL statement is used to retrieve information of a certain customer.
SELECT * FROM CommunityLibraryDatabase WHERE City='LongBeach';
The following SQL statement is used to update the residence information of a certain customer.
UPDATE CommunityLibraryDatabase
SET State='IL', City='Chicago'
WHERE FirstName='Alen' AND LastName='Tim'
The following SQL statement is used to retrieve names and phone number of customers who have overdue books as of a certain date.
SELECT Name, PhoneNumber FROM CommunityLibraryDatabase WHERE BookOverdue='2011/12/24';
You’re 80% through this paper. Sign up to read the full paper.
Sign Up Now — Instant Access Already a member? Log inAlways verify citation format against your institution’s current style guide requirements.