Sign Up Now
Get instant access to over 1 million+ study documents
OR
Already registered? click here to login
By creating your account, you agree to our terms of service, privacy policy and student honor code.
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
EMAIL
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
EMAIL
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
EMAIL
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...
First, I have learned the techniques of creating database using SQL statement from DB2 Express environment. The experience has improved my skills on the method to design a database without making mistakes. Moreover, I have learned the technique of normalizing a database. The normalization is very critical in the database design because it enhances the overview of the entire database and the functions of particular table in the database. With integration of normalization in the database, it will be easy to retrieve data from the database and it will be easy to generate report from the database. Moreover, I have learned the technique of using SQL statement to retrieve, update and delete data from the database. The experience I have gained from this assignment has enhanced my greater understanding on the database design and management.
PART 6 OF THE ASSIGNMENT
Report for Students who live in California
Students who live in California are six in number and the following code is used to retrieve students who live in California.
SELECT * FROM StudentInformationTable
WHERE State='CA'
TABLE OF STUDENTS WHO LIVE CALIFORNIA
STUDENTID
STREETNUMBER
STREET NAME
CITY
STATE
EMAIL
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
Students who live outside in California
The following SQL statement is used to retrieve information of Students who live out California.
SELECT * FROM StudentInformation Table
WHERE State= 'CA'
TABLE OF STUDENTS WHO LIVE OUTSIDE CALIFORNIA
STUDENTID
STREETNUMBER
STREET NAME
CITY
STATE
EMAIL
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
The following SQL statement is used to retrieve name of students who take ITM440 course.
SELECT * FROM StudentInformationTable
WHERE CourseTitle='ITM440Course'
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
The following SQL statement is used to retrieve name of students who live in California and take ITM440 course.
SELECT * FROM StudentInformationTable
WHERE CourseId='ITM440'
AND State='CA'
TABLE OF STUDENTS WHO LIVE CALIFORNIA AND TAKE ITM440 COURSE
STUDENTID
STREETNUMBER
STREET NAME
CITY
STATE
EMAIL
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
The following SQL statement is…
References
Allen, S. And Terry, E. (2005), Beginning Relational Data Modeling. Chapter 2. Introducing Relational Theory . Springer. USA.
Johnson, R. (2011).Functional dependencies, decompositions, normal forms. University of Toronto.
Litwin, P. Getz, K. & Gunderloy, M. (2002).Fundamentals of Relational Database Design. Microsoft Access 2 Developer's Handbook. USA. Sybex Inc.
Orr, N.(2007). Understanding and using the Database Design Report - Part 1. Goya Pty Ltd.
Database Security The focus of this study is that of database security. Databases and database technology are such that play critical roles in the use of computers whether it be in business, electronic commerce, engineering, medicine, genetics, law, education or other such entities requiring the use of computer technology. A database is quite simply a collection of data that is related such as a database containing customer information, supplier information, employee
Database Security Plan and Requirements Definition for a University Department The database security plan and requirements definition were developed. The plan included, at the outset, the inclusion of major stakeholder at the University and described their roles in initiating, implementing, and maintaining the plan. Individuals responsible for daily and other periodic tasks were developed. A major consideration in planning the security was the policy that governs granting of access. The need-to-know,
Database Design for Spa/Hotel Booking System The WR (Woodlands Retreat) is a medium sized organization currently intending to build a database system to integrate modern IT (information systems) in its business operation to enhance its dynamic and innovative business operations. Although, the Woodlands Retreat has an IT system, however, the current systems are not capable of keeping book records as well as not capable of managing all data for the company
This is necessary to provide a seamless platform on which health solutions can be effectively integrated and deployed. Without using such a platform, the development of electronic health care facilities will be more difficult to deploy. In other words, Tele-health is part of the overall healthcare ICT (Information Communications Technology) solutions that enables healthcare to be pushed out to the edge, for local delivery, and to be more evenly,
Design criteria exist at the levels of the technical, system integration aspects of the database to other systems through XML. This integration is critically important to ensure that the applications created can be effectively used over time and not have any scalability issues. There is also the need for designing the databases at the presentation layer to provide for scalability and flexibility of being able to create applications relatively quickly
Database Administrator, Manager of IT, and the value of a college education As a result of swift distribution of computers and information technology, a necessity for well trained workers to design and promote new hardware and software systems and to integrate new technologies. These workers comprises of computer systems analysts, database administrators, and computer scientists. Based on the choices and procedures of employers and on development of new areas of specialization