Using the previous assignment (see below) Use SQL command to:
- Create related tables to capture sales data and sales transactions.
Populate all tables with the related data.
Update the database when new products arrive.
Update the database when a sale is made.
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 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”);
create table employee