Q1. [50 points] Functional Dependency (FDs) a. [ 15 points] What are properties of a good/bad database design? What is a functional dependency (FD)? What are the possible sources of information that...

Q1. [50 points] Functional Dependency (FDs)
a. [ 15 points] What are properties of a good/bad database design? What is a functional dependency (FD)? What are the possible sources of information that defines the FDs that holds among the attributes of a relational schema?
Answer:
Properties of good database design as below:
1. Understand the purpose of data so that it can be stored and retrieved efficiently.
2. Database design should follow and implement Normalisations so that performance will be best balanced between querying and doing crud operations.
3. Avoid data redundancy .
4. Implement referential integrity on data by adding constraints.This will help to reduce business logic where susceptible human errors are expected.
5. Take full advantage of DB engine features.
6. Proper indexing on columns to improve performance.
7. Efficient Readability of the database by using proper naming conventions.
Functional Dependency:
A relationship between attributes is called as FD. In FD we can get the value of another attribute from given attribute. For example, If we know the value of employee id, we can obtain emp address, phone etc.
FD helps us to maintain the quality of data and aso helps to find the diff between good and bad database design.
IN FD Within a table between PK and other non-key it’s relationship between two attributes. For any relation r, attribute y is functionally dependent on attribute x(usually the PK), if for every valid instance of x, that val of x uniquely gives the value of y.
b. [ 20 points] Ruling Out FDs: Given a relation “MyTinyStore”. Which FDs may exist in this relation? What do you need to rule out?
MyTinyStore
    Customer_LastName
    Drink_Item
    Food_Item
    Delivery_method
    Smith
    Iced_Tea
    Burger
    Not_Delivery
    Smith
    Coke
    Hot Dog
    Delivery
    Paul
    Coke
    Hot Dog
    Carry out
    Sara
    Coffee
    Noodles
    Carry out
Answer :
i. Customer_lastname need to be removed and Customer id need to be added.
ii. Drink Item & Food Item need to be removed and item_id need to be there. Need to have a different table as food_items where w e can store item_id, item_type(drink,food),item name. Now item_id we have to store in MyTinyStore table as foreign key.
iii. We can add one more primary key column as order_id.

c. [15 points] Consider the two relations : COURSE and SECTION. Using SQL commands show that how a functional dependency can be derived from single key and multi-column keys. [ Hints: see from https://dev.mysql.com/doc/refman/5.7/en/group-by-functional-dependence.html ]
Also, explain how functional dependencies can be used to indicate the following:
(i)  A one-to-one relationship set exists between entity sets COURSE and SECTION.
(ii) A many-to-one relationship set exists between entity sets COURSE and SECTION.
Anwer :
Functional dependency derived from single key :
SELECT cr.Course_name, COUNT(*)
FROM SECTION sc, COURSE cr
WHERE sc.Course_number = cr.Course_number
GROUP BY cr.Course_number;
Functional dependency derived from multi-column keys :
SELECT cr.Course_name, sc.Instructor,cr.Department,sc.Semester
FROM SECTION sc, COURSE cr
WHERE sc.Course_number = cr.Course_number
GROUP BY sc.CountryCode, sc.Year;
From SECTION table using section_identifier if you want to retrieve course name and department then we have to do left join with COURSE table by using course_number key which is primary key in COUSE table. Here we can see there is an many-to-one relationship set exist between course and section table
Similarly if we want to have a list of all courses along with that if we need all the instructor name associated with the course then we have to do outer join with key course_number on table SECTION.Here we have one-to one relationship set exist between course and section table
Q2. [50 points] Normalization
a. [ 15 points] What does the term unnormalized relation refer to? How can we develop normal forms from the 1NF to BCNF.
AnSwer :
In a relation if there ar continuation values then we have a tendency to referred to as it as unnormalized relation.Transitive dependencies, relations nested among othr relations also are thought-about as unnormalized relation.
An unnormalized relation may also contain relations nested among different relations, moreover as every kind of transitive dependencies.
An unnormalized relation doesnot have any traditional kind condition.In 1972 Codd UN agency 1st projected the standardization method. the method, that yield in a very top-down fashion by evaluating every relation against criteria for traditional forms and mouldering relations as necessary, so is thought-about as relation style by analysis.Initially Codd projected 3 traditional forms one NF, 2NF and 3NF. A stronger definition of 3NF referred to as BCNF was projected by Boyce & Codd. of these nornal forms ar supported a analytical tool. 1NFsplits relation schema into schemas that have atomic values as domain for allattribues and values of none of attribute is ready of values. 2NF removes all partial dependencies ofnonprime attributes A in R on key and make sure that all nonprime attributes ar absolutely functionallydependent on the key of R. 3NF removes all transitive dependencies on key of R. and guarantee thatno non prime attribute is transitively addicted to key
.
b. [ 25 points] Consider the schema Customer (order file figure 9-1). Is this schema normalized? If yes, explain. If not, normalize it and explain. [ Hints: you can decompose the table to multiple tables if necessary]

Answer:
No its not normalized .We can normalized it by doing following steps:
· Move the customer last name and firstname in to diff table as Customer_profile and add columns like CustID,LastName,FirstName,PriorCustomer.
· Create a transaction table where we can store tax information ,before tax amount ,after tax amount,discount,before discount,after discount etc.In this table we can move Amount,Tax etc and use the transaction id in this order table
· So in order table we will have below columns : OrderNumber,Date,TransID,CustID,Total,PaymentType
· In transaction table we will have: TransID,Tax,Amount
· In Customer_profile tabl we will have CustID,LastName,FirstName, PriorCustomer
c. [ 10 points] Briefly explain about multi-valued dependency, and higher normal forms (e.g. 4th normal form)
Answer :
To contend with the matter of BCNF, R. Fagin introduced the concept of multi-valued dependency (MVD) and therefore the fourth normal form (4NF). A multi-valued dependency (MVD) may be a useful dependency wherever the dependency could also be to a group and not simply one price.
it's outlined as X →→ Y in relation R (X, Y, Z), if every X price is related to a group of Y values during a method that doesn't depend upon the Z values. Here X and Y are each subsets of R.
The notation X →→ Y is employed to point that a group of attributes of Y shows a multivalent dependency (MVD) on a group of attributes of X.
Q3: [ 50 points] Data stores and indexing
a. [15 points] What do you mean by database indexing? Write some SQL commends to index using B+- tree, and for hashing, encryption, and compression functions.
Answer:
Indexes are accustomed quickly find information while not having to go looking each row in an exceedingly information table on every occasion a information table is accessed. Indexes may be created mistreatment one or additional columns of a information table, providing the idea for each fast random lookups and economical access of ordered records.
B-Tree Index
SELECT * FROM COURSE WHERE Course_number LIKE 'cs1310%';
SELECT * FROM SECTION WHERE Course_number LIKE '%cs%1310%';
Hashing
SELECT PASSWORD('xyz') from users where username=’demo’;
Encryption
SELECT SHA1(‘accountno’) from users where username=’demo’;
SELECT AES_ENCRYPT('ABC', 'key') from users where username=’demo’;
SELECT DES_ENCRYPT('geeksforgeeks', 5) from users where username=’demo’;
Compress
SELECT COMPRESS(imagecotent), LENGTH(COMPRESS(imagecotent)) from logo;
b. [20 points] A PARTS file with Part# as the key field includes records with the following Part# values: 20, 69, 37, 61, 47, 92, 48, 71, 56, 59, 18, 21, 10, 74, 78, 15, 16, 20, 24, 28, 39, 43, 47, 50, 69, 75, 8, 41, 33, 38. Suppose that the search...