Data base (SQL)homework

1. Create related tables to capture sales data and sales transactions.

2. Populate all tables with the related data.

3. Update the database when new products arrive.

4. Update the database when a sale is made.

5. Describe (in at least a half page) the major points you learned on SQL and what lessons you have learned in this exercise.

Normalization is the process of organizing databases in the form of tables. It is a technique of eliminating redundancy from database. It makes database easy to maintain and efficient to use. When a table in a database reconstructed into multiple tables to eliminate certain dependency in each table, if dependency is not eliminated then the whole process is repeated until the dependency is eliminated. There are different forms of Normalization:

First Normal Form1NF: It refers to the database table, eliminating repeating sets of data elements and creates individual table of each sets of related data. There is no repetition of any value and attributes in the table as each column holds one attribute and each row holds primary key i.e. unique. Second Normal Form 2NF: It should be 1NF. It doesn’t have partial dependency of any column in primary key and refers to non-prime attribute is dependent on any candidate key. Third Normal Form 3NF: It should be 2NF. There should be no transitive dependency in the table and all the attributes of table depends must depends on candidate key of the table. Example: Online Transaction Processing (OLTP).

Boyce Codd Normal Form BCNF: It should be 3NF. There should be no functional dependency in the table and it must not depend on multiple candidate keys so, that redundancy is improved.

Importance of Normalization:

· Normalization is the technique to minimize data redundancy and also reduce storage requirements.

· It provides unique identification (primary key) from records in a database.

· It eliminates unwanted dependencies from a database table.

· It describes database efficiently.

· It is used for simple retrieval of database designs.

· It determines the nature of data and also gives understanding of data structure and databases.

· Ability to access database quickly and without adjusting consistency and integrity.

My tables are already normalized from case 2. It meets the requirements for 1NF, it has different tables for each set of related data, and there is no repetition of any value and attributes in the table as each column holds one attribute and each row holds primary key. Each column in my tables that is not a determiner of the contents of another column is itself a function of the other columns in the table, thus satisfying the 2NF. Finally my tables allow for things such as pricing to be tracked separately without affecting data in other tables satisfying the requirements for the 3NF.

ACID Properties:

ACID properties is the major concept of database transactions. It ensures reliability and provides safe sharing of data. It stands for Atomicity, Consistency, Isolation and Durability.

Atomicity: It ensures all data transaction as one atom (whole unit) which means transaction is fully committed or completely rollback.

Consistency: It ensures valid data, if there is some invalid data in transactions, database will return back to its previous state. So the data should be authorized with the person.

Isolation: It ensures concurrent transactions. It allows multiple users accessing the same data at the same point of time. It provides integrity and consistency to databases.

Durability: It ensures no single point of failure during transactions. It maintains the committed transactions in the database at the time of system or storage failure.

Need of Normalization:

Normalization ensures redundancy of databases to make it efficient and also eliminating unnecessary data dependencies from the database relationships. It also ensures minimum storage space so that logical data is stored. There is no duplicate data stored in the databases. It also minimize the need of reconstructing data structures and increase the life span of databases. It make relational databases more descriptive and easy to maintain.

Optimization in Normalized databases is achieved by analyzing various small portions of data that is stored in the relational database which is correlated with each other with their dependencies. Analysis of normalization must achieve four goals.

· By arranging data into tables as small logical groups.

· By minimizing data redundancy in the database.

· To maintain integrity of database manipulating data quickly and efficiently.

· Maintaining data in the form, when we modify it, it should be kept at one place.

Let us take an example of Optimizing normalization. Suppose, we are maintaining database of Canada in a table, and after sometime Quebec decided as not to be a part of Canada anymore. In this situation we need to write large code and complex queries to search data and instance of the Quebec and gets changed accordingly. It takes lots of time and become very complex and difficult. In accordance of solving this issue, we use normalization. We need to find a table named like tableProvince, and make the changes in it that will effectively change all the entries related to that table relationships. This practice ensures integrity and consistency throughout the table and provide normalized data.

This is how you would create a table using SQL statements.

create table "tablename"

("column1" "data type",

"column2" "data type",

"column3" "data type");

For example:

create table employee

(first varchar(15),

last varchar(20),

age number(3),

address varchar(30),

city varchar(20),

state varchar(20));

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

MY TABLES NORMALIZED

Entity Set Description

CLIENT:

Name

Cust_ID

Name

Phone

Email

ValueRange

Positive

number

ASCII

ASCII

DateLastTrans

Last shopping date.

Date

Any date equal to or greater than DateCreated

DateCreated

Date joined, first trans.

date

Valid

numericchars

Anydate

beforepresent

Default

Value

NULL

Allowed?Unique?

Singleor Multi-value?

SimpleorComposite?

Next

available

Passedvalue

null

null

null

Passed

value

NO

NO

YES

YES

YES

NO

YES

NO

NO

NO

NO

NO

Single

Single

Single

Single

Single

Single

Simple

Simple

Simple

Simple

Simple

Simple

Candidate keys: Cust_ID,Name, Phone,Email

Primary key:Cust_ID

Weak/Strong: strong

Fieldstobeindexed:Cust_ID

DEPENDENTS:

Candidate keys:none

Primary key:none

Weak/Strong:weak

Partial key:Name

Fields to be indexed:Name

1 | Page

ELECTRONIC_EMPLOYEES:

ELECTRONIC_EMPLOYEES:

Candidatekeys:Emp_ID,SSN,Email,Name

Primarykey:Emp_ID

Weak/Strong:strong

Emp_IDCHECKOUT:

Name

Checkout_ID

Cust_ID

Date

Store_ID

Subtotal

Emp_ID

Tax

Description

Specific checkout number

FKfrom CustomerID

Date of checkout

Foreign key

from Store table

Amount to fall items on list

Employee who performed checkout

Tax amount on subtotal

Domain/Type

Number(9)

Number(7)

date

Number(3)

double

Number(7)

double

ValueRange

integer

integer

Present date

integer

Positivenumber

integer

Positive number

DefaultValue

Nex tavailable

FKvalue

Auto generated

FKvalue

null

FKvalue

null

NULL

Allowed?

NO

NO

NO

NO

NO

NO

NO

Unique?

YES

YES

NO

YES

NO

YES

NO

Singleor Multi-value?

single

Single

Single

Single

Single

Single

Single

SimpleorComposite?

simple

Simple

Simple

Simple

Simple

Simple

Simple

Candidatekeys: Checkout_ID,Cust_ID,Electronic_ITEMS_ID

Primarykey: Checkout_ID

Weak/Strong:strong

Fields to be indexed:Checkout_ID

ELECTRONIC_ITEMS:

ELECTRONIC_ITEMS: (continued)

Candidate keys: Item_ID,UPC

Primarykey:Item_ID,UPC

Weak/Strong:strong

Fieldstobeindexed:Item_ID,UPC

Candidatekeys:Electronic_ITEMS_ID

Primarykey:Electronic_ITEMS_ID

Weak/Strong:strong

Fieldstobeindexed:Electronic_ITEMS_ID

Page14of88

STORE:

Candidatekeys: Electronic_ITEMS_ID,Item_IDPrimarykey:Electronic_ITEMS_ID,Item_IDWeak/Strong:strong

Fieldstobeindexed:Electronic_ITEMS_ID,Item_ID

Notes:Electronic_ITEMS_IDisneededheretotietheElectronic_ITEMStothequantityforreportsthatareElectronic_ITEMSspecific

Page15of88

Candidatekeys:Checkout_ID,Item_IDPrimarykey:Checkout_ID,Item_IDWeak/Strong:strong

Fields to be indexed: Checkout_ID,Item_ID

Page16of88

CHECKOUTACTION:

MANAGER

Candidate keys: Checkout_ID,Item_ID

Primary key:Checkout_ID,Item_ID

Weak/Strong:strong

Fields to be indexed: Checkout_ID,Item_ID

Page 18of88