discussion Database

DB#2

IT 403

Baraa Adel-16033112

Q1: You are requested to submit 1NF and 2NF examples with explanation.

First Normal Form (1NF)

The First normal form simply says that each cell of a table should contain exactly one value. Let us take an example. Suppose we are storing the courses that a particular instructor takes, we can store it like this:

Instructor's name	Course crn
Dr. Yousef	(CS101, CS154)
Dr. Ahmed	(CS152)

Here, the issue is that in the first row, we are storing 2 courses against Dr.Yousef. This isn’t the optimal way since that’s now how SQL databases are designed to be used. A better method would be to store the courses separately. For instance:

Instructor's name	Course crn
Dr. Yousef	CS101
Dr. Yousef	CS154
Dr. Ahmed	CS152

This way, if we want to edit some information related to CS101, we do not have to touch the data corresponding to CS154. Also, observe that each row stores unique information. There is no repetition. This is the First Normal Form.

Second Normal Form (2NF)

Course crn	Course lecture	Instructor Name	Instructor’s phone number
CS101	Lecture Hall 20	Dr. Yousef	+966 537522000
CS152	Lecture Hall 21	Dr. Ahmed	+966 502369963
CS154	CS hall	Dr. Yousef	+966 578001214

Here, in this table, the course crn is unique. So, that becomes our primary key. Let us take another example of storing student enrollment in various courses. Each student may enroll in multiple courses. Similarly, each course may have multiple enrollments. A sample table may look like this (student name and course crn):

Student name	Course code
Mohammed	CS152
Waleed	CS101
Mohammed	CS154
Kareem	CS101

Here, the first column is the student name and the second column are the course taken by the student. Clearly, the student name column isn’t unique as we can see that there are 2 entries corresponding to the name ‘Mohammed’ in row 1 and row 3. Similarly, the course code column is not unique as we can see that there are 2 entries corresponding to course code CS101 in row 2 and row 4. However, the tuple (student name, course code) is unique since a student cannot enroll in the same course more than once. So, these 2 columns when combined form the primary key for the database.

As per the second normal form definition, our enrollment table above isn’t in the second normal form. To achieve the same (1NF to 2NF), we can rather break it into 2 tables:

Students:

Student name	Enrolment number
Mohammed	1
Waleed	2
Kareem	3

Here the second column is unique and it indicates the enrollment number for the student. Clearly, the enrollment number is unique. Now, we can attach each of these enrollment numbers with course codes.

Courses:

Course code	Enrolment number
CS101	2
CS101	3
CS152	1
CS154	1

These 2 tables together provide us with the exact same information as our original table.

Q2: Discuss the need of Normal Forms.

Normal forms: Certain rules in database management system design have been developed to better organize tables and minimize anomalies. The stage at which a table is organized is known as its normal form (or a stage of normalization). There are three stages of normal forms are known as first normal form (or 1NF), second normal form (or 2NF), and third normal form (or 3NF). As a table progressively satisfies the conditions of the different normal forms, it's less prone to the anomalies discussed earlier.

Q3: Discuss the need of 2NF means why we need 2NF.

It’s important because clarity to the database design, makes it easier for us to describe and use a table, and tends to eliminate modification anomalies. This stems from the primary key identifying the main topic at hand, such as identifying buildings, employees, or classes, and the columns, serving to add meaning through descriptive attributes. An EmployeeID isn’t much on its own, but add a name, height, hair color and age, and now you’re starting to describe a real person.

Q4: Discuss the drawback of 1NF.

Translating a table into 1NF alleviates much of the data redundancy and wasted space of a table that has not been normalized.

It cannot support multi valued attributes.

It does not suffer from redundancy and having no limit to place on a number of values.

Q5: Discuss Atomicity and multivalued attribute

Atomic (or single valued): an atomic attribute is always represented by a single value for a particular entity. For example, a person’s status is always an atomic attribute. Most attributes are atomic attributes.

Multivalued: A multivalued attribute may have one or more values for a particular entity. For example, Location as the attribute of an entity called ENTERPRISE is multivalued, because each enterprise can have one or more locations.

Q6: Discuss functional dependency 

Functional dependency in a database serves as a constraint between two sets of attributes. Defining functional dependency is an important part of relational database design and contributes to aspect normalization.