DATABASE GRADED ASSIGNMENT #3

NAME: USMAN SULEMAN

PROGRAM: BCS 4TH (A)

Q#1 What is Functional dependency, Partial dependency, fully functional dependency and
Transitive dependency in database?
1. Functional dependencies
If the information stored in a table can uniquely determine another information in the same table, then it is
called Functional Dependency. Consider it as an association between two attributes of the same relation. If K
functionally determines L , then
K -> L

Example:
<student>

Std_ID Std_name Std_age

S101 Usman 18
S102 Komail 28
S103 Ahad parvaiz 45

In the above table, Std_name is functionally dependent on std_ID because std_name can take only one
value for the given value of std_iD

Std_ID -> Std_name

2. Fully functional dependencies:

The term full functional dependency is used to indicate the minimum set of attributes in of a functional
dependency. In other words, the set of attributes X will be fully functional dependent on the set of attributes
Y if the following conditions are satisfied:

 X is functionally dependent on Y and

 X is not functionally dependent on any subset of Y.

<project budget>

projectID Project budget

P1162 1500
P1612 1600

<worker project>
Worker_iD projectiD Hrs. spent
W1312 1162 50
W1902 1612 29

The above relations states worker_ID, ProjectID, Project budget -> Hrs. spent However, it is not fully functional
dependent. Whereas the subset {worker_ID, ProjectID} can easily determine the {Hrs} spent on the project by the
worker. This summarizes and gives our fully functional dependency
3. Partial dependency
Partial dependency means that a nonprime attribute is functionally dependent on part of a candidate
key. (A nonprime attribute is an attribute that's not part of any candidate key).

Std_ID Std_Name Course_code Course_name

S1151 Zeeshan Cs101 Theory of automata
S1019 Afnan Cs102 Data analysis

In the above table, we have partial dependency. The prime key attributes are Std_ID and Course_code. As
stated, the non-prime attributes i.e. Std_Name and Course_Name should be functionally dependent on part
of a candidate key, to be Partial Dependent. The Std_Name can be determined by Std_ID that makes the
relation Partial Dependent. The course_Name can be determined by course_code, which that the relation
Partial Dependent.

4. Transitive dependency
When an indirect relationship causes functional dependency. it is called Transitive Dependency. A transitive
is a type of functional dependency which happens when it is indirectly formed by two functional
dependencies. i.e. If K-> L and L-> J is true, then K-> J is a transitive dependency.

Author_ID Author Book Author_Nationality

Auth_001 Orson Scott Card Ender's Game United States
Auth_001 Orson Scott Card Children of the Mind United States
Auth_002 Margaret Atwood The Handmaid's Tale Canada

Here in the above table we have transitive dependency. the Book attribute determines
the Author attribute. If you know the book name, you can learn the author's name.
However, Author does not determine Book, because an author can write multiple books. Likewise,
the Author attribute determines the Author_Nationality, If we know the book name, we can
determine the nationality via the Author column.

Q# 2 What is normalization in database? Relation STUDENT in table below is not in

1NF, why? Also decompose it into 1NF

Normalization:
Normalization is a database design technique that reduces data redundancy and eliminates
undesirable characteristics like Insertion, Update and Deletion Anomalies. Normalization rules
divides larger tables into smaller tables and links them using relationships. The purpose of
Normalization in SQL is to eliminate redundant (repetitive) data and ensure data is stored logically
It is a process of organizing the data in database

Types
Here are the most commonly used normal forms:

 First normal form(1NF)

 Second normal form(2NF)
 Third normal form(3NF)
 Boyce & Codd normal form (BCNF)
STUD_NO STUD_NAME STUD_PHONE STUD_STATE STUD_COUNTRY
1 ASLAM 9716271721 SINDH PAKISTAN
9871717178
2 JAVAID 9898297281 PUNJAB PAKISTAN
3 SUBHAN PUNJAB PAKISTAN

The above table is not in the 1NF because according to the definition of 1NF, A relation is in first normal
form iff every attribute in every tuple contains an atomic value but in the above table you can see that in
STUD_PHONE attribute in first tuple there are two values and in the last tuple there is no value so that’s
why this table is not in the first normal form(1NF)
Now we are going to decompose the above table into 1NF:

STUD_NO STUD_NAME STUD_PHONE STUD_STATE STUD_COUNTRY

1 ASLAM 9716271721 SINDH PAKISTAN
2 JAVAID 9898297281 PUNJAB PAKISTAN
3 SUBHAN 9881818188 PUNJAB PAKISTAN
Now
this table is in 1NF because now in the above table every tuple contain atomic value there is no multiple
values and no null values.

Q#3 What is physical database design? Also explain different components of PDD?
Definition:
Physical database design is the last stage of database design.
The objective of physical database design is to implement the database as asset of stored records, files,
indexes and other data structures.
These data structures provide performance and also ensure data integrity, security, recoverability
Components of physical database:
There are five components of physical database design

 Data volume and usage analysis

 Data distribution strategies (centralized, partitioned, replicated, hybrid)
 File organization
 Indexes
 Integrity constraints

1) Data volume and usage analysis:

it includes the size and usage of the data base it also estimates the cost and average.
File organization and access methods are also analyzed at this stage
2) Data distribution strategies:
in order to achieve performance and quick response of database organizations use data distribution
strategies.
For the data distribution, organization at the time of physical design face problems that at which node and
what data will be placed.
a) Data distribution strategies centralized:
All data units are placed at one location it is easy to implement communication may be high in some cases
Data are not readily accessible by remote users
If the central location gets fail the whole database gets down.
b) Data distribution strategies partitioned:
In this distribution type the database is divided into fragments or partitions
Each fragments/partition is placed at different site.
It is more accessible then centralized database strategy if one location get fail it does not affect thee other
partitions it also reduce data processing cost
c) Data distribution strategies replicated:
Data replication is the process of storing separate copies of the database at two or more sites. It is a
popular fault tolerance technique of distributed databases.
Advantages:
In case of failure of any site, the database system continues to work since a copy is available at another
site.
Availability of local copies of data ensures quick query processing and consequently quick response time.
It reduces the network load

Disadvantages:
Maintaining multiple copies of data is associated with increased storage costs. The storage space required
is in multiples of the storage required for a centralized system.
Each time a data item is updated, the update needs to be reflected in all the copies of the data at the
different sites. This requires complex synchronization techniques and protocols
d) Data distribution strategies hybrid:
In this strategy database is divided into critical and non-critical fragments
The non-critical fragments are stored at only one site and critical fragments are stored at multiple sites

3) File organization:
It is a technique used to physical arrange the records of a file on a secondary storage device.
Important points:
 Minimizing need for reorganization
 High throughput for processing transactions
 Fast access for retrieval of data
 Accommodating growth
 Protection from failure of lost data
 Efficient use of storage of space

4) Indexes:
 it is use to locate a record file in a file more quickly.
 it is use to speed up the sorting and searching procedure.
 some indexes are created automatically when relationships are created in related tables.
 the index may be created on primary key, secondary key, foreign key etc.
 the indexes are stored in index file.
 DBMS uses these files to improve their performance.
5) Integrity constraints:
 Integrity means the correctness and completeness of data to make database consistence.
 Integrity is usually expressed in terms of certain constraints which are the consistency rules.
 Data base are not permitted to violate these rules.
 It also concerned with the quality of data.
 These are rules that are designed to keep the data consistent and correct.