7ZCtaFap

1Database is consider as backbone of any computer application or software. A relational database organizes data in tables (or relations) The Structural view of relations and attributes(columns) and their relationships with other relations helps to understand dataflow between relations.
2
3A well-designed database shall eliminate Data Redundancy: the same piece of data shall not be stored in more than one place. This is because duplicate data not only waste storage spaces but also easily lead to inconsistencies.Also well-designed database ensures Data Integrity and Accuracy.
4
5In order to attain data integrity and consistency, there are some requirements and design process that are needed to be followed while designing a database schema. The fact is most people are concerned with "defining attributes for their relation" rather than finding how this attributes relate with other atrributes in the relation.Avoiding such requirements and process can deeply affect the database design.
6The requirements listed below influence the detailed structure of schemas that are produced.
7
8The most crucial aspect in designing a relational database is to identify the relationships among tables.The power of relational database lies in the relationship that can be defined between tables.The types of relationship include:
9a)One-to-One Relationship
10
11b)One-to-many or Mant-to-One relationship
12
13c)Many-to-Many Relationship
14
15a)One-to-One Relationship: In a one-to-one relationship, one record in a table is associated with one and only one record in another table.For example, in a school database having student and student-info table, each student is associated with one student ID in student-info table, and each student ID is assigned to only one student. 
16
17
18b)One-to-Many Relationship: In a one-to-many relationship, one record in a table can be associated with one or more records in another table. For Example, considering customer and order table, a customer can have many orders while a particular order will belong to one particular customer.
19
20
21c)Many-to-Many Relationship: Many-to-Many relationship is a bit complex relationship in which many records in a table can link to many records in another table.For example, consider a customer and product table, a customer can buy many products and a product can be buyed by many customers.In order to define Many-to-Many relationship we would require a third table called as junction table.This third table will have primary key from customer table and product table to show association between the two table.This junction table will uniquely identify which products belongs to which customer since it will have product id from product table and customer id from customer table.
22
23
24Establishing consistent relationships between database tables helps ensure data integrity, contributing to database normalization.
25
26There are some anomalies that every database designer should be aware of which is called Database Anomalies.
27
28In relational database design, we not only want to create a structure that stores all of the data, but we also want to do it in a way that minimize potential errors when we work with the data. In particular, SQL can be used to manipulate data in the following ways: insert new data, delete unwanted data, and update existing data. Similarly, in an un-normalized design, there are 3 problems that can occur when we work with the data:
29
30a)Insert Anomaly
31
32b)Delete Anomaly
33
34c)Update Anomaly
35
36Insert Anomaly:An Insert Anomaly occurs when certain attributes cannot be inserted into the database without the presence of other attributes.
37
38For example consider StudentCourse table having studentId, courseId, studentName, address, course i.e StudentCourse(studentId,courseId,studentName,address,course). Now here, we can't add a new course unless we have at least one student enrolled on the course. This will be problamatic while inserting data into the table.
39
40
41Delete Anomaly: A Delete Anomaly exists when certain attributes are lost because of the deletion of other attributes.
42For example, considering the previous table, what happens if Student with studentid lets say S05 is the last student to leave the course - All information about the course is lost, i.e if we remove a record of student from the table, then the course associated with that student will also be deleted and that means we no longer have information about that course.
43
44
45Update Anomaly: An Update Anomaly exists when one or more instances of duplicated data is updated, but not all. 
46For example, consider changing address of a particular student in the previous example - you will need to update all instances of that particular student's address or else it will lead to data inconsistency.If there are many instances of that particular student then updating every instance can be a tedious job.
47
48
49To address the 3 problems above, we go through the process of normalization. When we go through the normalization process, we increase the number of tables in the database, while decreasing the amount of data stored in each table.
50
51The next important topic to study before designing any database schema is NORMAlIZATION.
52
53
54If you've been working with databases for a while, chances are you've heard the term normalization. Perhaps someone's asked you "Is that database normalized?" or "Is that in 3NF?" Normalization is often brushed aside as a luxury that only academics have time for. However, knowing the principles of normalization and applying them to your daily database design tasks really isn't all that difficult and it could drastically improve the performance of your database.
55
56Now, What is Normalization? Normalization is the process of efficiently organizing data in a database.This process takes a relation schema through a series of test to certify whether it satisfies a certain normal forms. There are two main goals of the normalization process: eliminating redundant data(storing the same data in more than one table) and ensuring data dependencies(only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.
57
58
59Normal Forms:There is series of guidelines for ensuring that databases are normalized. These are referred to as normal forms.
60
61FIRST NORMAL FORM (1NF): A database is in first normal form if it satisfies the following conditions:
62a)Contains only atomic values.
63b)There are no repeating groups.
64An atomic value is a value that cannot be divided.
65
66How do we bring an unnormalized table into first normal form? Consider the following example:
67
68Consider a product table with attributes productID, Color, and Price.Product(ProductId,color,price).
69
70Now consider its values productID=1, color=blue,Green , price=20 .Product((1,'blue''green',20),(2,'red',15))
71As you can see [color] column has two values blue and green.This table is not in first normal form because the [Color] column can contain multiple values. The values in the [Color] column in the first row can be divided into "blue" and "green".
72
73To bring this table to first normal form, we split the table into two tables and now we have the resulting tables:
74
75product_price(productId,Price) and product_color(productId,color).Now this tables can have attributes like product_price((1,20),(2,15)) and product_color((1,'blue'),(2,'red'),(1,'green')).
76Now first normal form is satisfied, as the columns on each table all hold just one value and we can still uniquely identify product and its color.
77
78The resion we cant have multiple values for a particular column is because it can affect database performance while querying the database.From the above example of unnormalized table, as you can see [colour] column contains multiple value. Now, If I need data of all the productId whose color is green, I will have to make multiple comparison for a particular record(row). Since a particular row has 2 or more values i will have to right a logic to compare it with each value and give me back the desired result which is a very tedious process.
79
80Another important condition that should be met if a table is to be in first normal form is that it should not have any repeating columns representing the same information.
81
82For example, Consider a Unnormalized FlimAgent table with attributes AgentID,AgentName,AgencyName,OfficeLoction,customer1,customer2,customer3.
83
84As you can see we have group of columns i.e 3 columns customer1, customer2, customer3 representing the same information
85Now, Problem here is that everytime i want to add a new customer i have to create a new column for it and this also leads to data redundancy.
86
87
88SECOND NORMAL FORM(2NF): A database is in second normal form if it satisfies the following conditions:
89
90a)It is in first normal form.
91b)All non-key columns are fully functional dependent on the primary key.
92
93Functional dependency is a relationship that exists when one column uniquely determines another column.
94
95If the primary key is a composite key(combination of 2 keys), all non-key column cannot be dependent on a subset of the primary key i.e all non-key column cannot dependent on 1 part of primary key.
96
97Consider the following example: purchase_detail(CustomerID,StoreID,PurchaseLocation).
98This table has a composite primary key [CustomerID, StoreID]. The non-key attribute is [PurchaseLocation]. In this case, [Purchase Location] only depends on [StoreID], which is only 1 part of the primary key and does'nt have to do anything with 2nd part of the primary which is [CustomerID]. Therefore, this table does not satisfy second normal form.
99
100To bring this table to second normal form, we break the table into two tables, and now we have the following:
101Purchase(CustomerID,StoreID) and Store(StoreID,PurchaseLocation).
102
103What we have done is to remove the partial functional dependency that we initially had. Now, in the table [STORE], the column [Purchase Location] is fully dependent on the primary key of that table, which is [StoreID].
104
105Note that if the primary key is not a composite key, all non-key attributes are always fully dependent on the primary key. A table that is in 1st normal form and contains only a single key as the primary key is automatically in 2nd normal form.
106
107
108THIRD NORMAL FORM(3NF): A database is in third normal form if it satisfies the following conditions:
109
110a)It is in second normal form
111b)There is no transitive functional dependency
112
113By transitive functional dependency, I mean we have the following relationships in the table: A is functionally dependent on B, and B is functionally dependent on C. In this case, C is transitively dependent on A via B i.e if A->B and B->C then A->C.
114
115Consider the following example: BookDetail(BookID,GenreID,GenreType,Price)
116In the table, [BookID] determines [GenreID], and [GenreID] determines [GenreType]. Therefore, [BookID] determines [GenreType] via [GenreID] and we have transitive functional dependency, and this structure does not satisfy third normal form.
117
118In an another example, we have faculty table with faculty(FacultyID,Name,Dept,DeptHead) we can see values of column DeptHead is dependent on Dept because department head will be specific for a department.This way a non prime column is dependent on non prime column.
119
120To bring BookDetail table to third normal form, we split the table into two as follows:Book(BookID,GenreID,Price) and Genre(GenreID,GenreType)
121Now all non-key column are fully functional dependent only on the primary key. In [BOOK] table, both [GenreID] and [Price] are only dependent on [BookID]. In [GENRE] table, [GenreType] is only dependent on [GenreID].
122
123Not using normalization was my biggest mistake when I first attemped to build database schema for my application.Later On, I realized that center of all problems which I faced were because my tables where not normalized.Normalizing tables may be tough task at first, but later on as you progress, you tend to understand it well.
124
125Generalization, Specialization and Inheritance.
126
127Now, In database schema design, there is an important concept which every database designer should know called INHERITENCE.
128If you are familiar with inheritance you know that inheritance enables child class(sub class) to inherit properties from parent class(super day). Same principle is used in database design.Inheritance enables you to share attributes between tables such that a sub table inherits attributes from its parent table.Basically you should know concepts like SPECIALIZATION, GENERALIZATION and attribute inheritance.
129
130The one main drawback of Entity-Relationship(ER) diagram is it does'nt support inheritance.To use Inheritance property, We have to use Enhanced Entity Relationship(EER) diagram, and the two main concept involved in it are generalization and specialization.
131
132
133GENERALIZATION: Generalization is a process of extracting common characteristics from two or more tables and combining them into a generalized table.So, it is a bottom up approach as two or more lower lever entities are combined to form a higher level entity.
134
135Consider an example database having 3 tables: CAR, TRUCK, MOTORCYCLE. The 3 tables might have some common attributes between them like vehicleID, vehicleName, price etc. Now since this tables have some common attributes between them, we put the shared attributes in a supertype in another table called generalized table.
136
137
138SPECIALIZATION: Specialization is opposite to Generalization. It is a top-down approach in which one higher level table can be broken down into one or more lower level tables.Its like if you a have a table and that table has some sub-type then that table can be broken down into sub-tables.This sub types(child) can inherit attributes from the main class(parent) and along with that it will have some attribute that belongs to that particular table only.
139
140Take ‘Person’ for example. A person has name, date of birth, gender, etc. These properties are common in all persons, human beings. But in a company, persons can be identified as employee, employer, customer, or vendor, based on what role they play in the company.
141
142Similarly, in a school database, persons can be specialized as teacher, student, or a staff, based on what role they play in school as entities.
143
144So for developing a database schema for any application all the above topics and concepts must be considered and understood well to make the database efficient, error free ,consistant and complete.