A1721964978 16469 13 2018 normalization and mcq [Compatibility Mode] PDF

Preview

Summary

System...

Description

Normalization • • • • •

Refine data To attain a good DB design To reduce redundancy Minimize storage space req Eliminate data anomalies

Decomposition • Non loss decomposition vs lossy decomposition • Un normalized table

Functional dependency • • • • •

AB DETERMINANT DETERMINED 1-a 1-a 1-b 2-a

1-a 2-b

1-a 1-a

1NF • Identify and remove repeating groups • Identify and remove non atomic attr • Identify the keys for the table

1NF • First (sid, status,city,pid,qty) Sid

Status

City

Pid

Qty

s1

20

Jal

p1

100

s1

20

Jal

P2

150

s2

10

Asr

P1

200

s2

10

Asr

P2

250

s3

10

Asr

P2

300

s4

20

Jal

P3

100

s4

20

jal

p4

150

2NF • A relation is in 2NF iff a)It is in 1NF b)Every non key attribute is fully depend on the primary key (remove partial dependencies) • Anomalies INSERT UPDATE

2NF • Break down the relation FIRST into: • SECOND(sid, status, city) and SP(sid,pid,QTY)

3NF • Elimination of transitive dependencies • Still anomalies are there DELETE UPDATE That’s why SP is further breakdown to : SC(sid, city) CS(city, status)

BCNF • A relation is said to be in BCNF iff: • It is in 3NF • All its determinants (i.e the attributes on which other attribute depends) are candidate keys • First (sid, status,city,pid,qty)--- not in BCNF • SECOND(sid, status, city) • SP(sid,pid,QTY) • SC(sid, city) • CS(city, status)

• Trivial − If a functional dependency (FD) X → Y holds, where Y is a subset of X, then it is called a trivial FD. Trivial FDs always hold. • Non-trivial − If an FD X → Y holds, where Y is not a subset of X, then it is called a non-trivial FD. • • • •

Examples: AA ABA ABC

More examples • • • • • • • •

R={A,B,C,D,E} {A->B,B->C,C->D,D->E,E->A) R={A,B,C,D} {A->B,B->C,C->D} A-Prime attribute B,C,D- Non Prime attribute R={A,B,C,D,E} {AB->C,C->D, D->E,A->B}

Relevant example • R={A,B,C,D,E} • {A->BC, AD->E, B->C) • • • • • •

A+ = ABC AD+ =ADEBC B+ = BC ABCDE -> ADE, BC (ABC) ADE AD->E ABC A->BC ATTRIBUTES)

B->C(NON KEY

CONTINUE • ABC -> • A->B • B->C

AB, BC

4NF • A relation is said to be in 4NF iff • It is in 3NF or BCNF • It does not contain any multi value dependencies

Rule to transform a relation into 4NF • A relation R having A, B,C as attributes can be non loss decomposed into two projections R1(A,B) and R2(A,C) iff: • MVD A-> -> B/C holds in R

5NF • • • •

A relation is said to be in 5NF iff Relation is in 4NF It can n’t be further non loss decomposed If join dependency exists in original relation then decompose the relation

Quiz Questions Q.1 An entity set that does not have sufficient attributes to form a primary key is a (A) strong entity set. (B) weak entity set. (C) simple entity set. (D) primary entity set Q.2 In a relational model, relations are termed as (A) Tuples. (B) Attributes (C) Tables. (D) Rows. Q.3 In the relational models, cardinality is termed as: (A) Number of tuples. (B) Number of attributes. (C) Number of tables. (D) Number of constraints. Q4. Key used to represent relationship between tables is called (A) Primary key (B) Candidate Key (C) Foreign Key (D) Super Key Q5. A set of possible data values is called (A) attribute. (B) degree. (C) tuple. (D) domain.

Quiz Questions Q6. In a relational model, columns are termed as (A) Tuples. (B) Attributes (C) Tables. (D) Attributes Q7. Logical design of database is called (a) Schema (b) Instance (c) database snapshot (d) none of above Q8. Snapshot of data in database at given instant of time is called (a) Schema (b) Instance (c) database snapshot (d) none of above Q9. Primary Key must be (a) NOT NULL (b) Unique (c) Option a or b (d) Option a and b Q10. The primary key is selected from the: A. composite keys. B. super key. C. candidate keys. D. foreign keys.

Quiz Questions Q.4 In which constraint type we can apply conditional operators? (A) primary key (B) Unique Constraint (C) Check (D) Foreign key Q.5 To delete a particular column in a relation the command use d is: (A) UPDATE (B) DROP (C) ALTER (D) DELETE

Q.6 A primary key is combined with a foreign key creates (A) Parent-Child relation ship between the tables that connect them. (B) Many to many relationship between the tables that c onnect them. (C) Network model between the tables that connect them. (D) None of the above. Q.7 choose the correct syntax to change the datatype of any attribute? a) alter table table_name alter column new datatype b) alter table table_name modify column_name new-datatype c) alter table table_name update column_name new-datatype d) alter table table_name modify column column_name new-datatype

Quiz Questions Q.8 choose the correct syntax to change the name of any attribute? a) alter table table_name alter column new_name b) alter table table_name modify column_name new-name c) alter table table_name rename column_name to new_name d) alter table table_name rename column column_name to new_name Q.9 Drop is a) ddl b) dml c) dcl d) svl

3) A weak entity type a)Must have total participation in an identifying relationship b)Does not have key attribute c)Both a and b d)None of the above 4) In the Network data model, relationship between records is implemented using _______. a)Keys b)Pointers c)parent-child relationship d)None of these 5) _______ constraint is used to maintain consistency among tuples in two relations. a) key b) domain c) referential-integrity d) entity-integrity

6) The null value of an attribute indicates ______ value. a) zero b) unknown c) infinite d) error 7) When there is more than one key in a relation, then each such key is called ______. a) primary b) useful c) multiple d) candidate 8) Foreign key is the one in which the ________ of one relation is referenced in another relation. a) Foreign key b) Primary key c) References d) Check constraint

Quiz 1Q: You need to display the last names of those employees who have the letter "A" as the second character in their names. Which SQL statement displays the required results? SELECT last_name FROM EMP WHERE last_name LIKE '_A%’; SELECT last_name FROM EMP WHERE last name='*A%’; SELECT last_name FROM EMP WHERE last name ='* _A%’; SELECT last_name FROM EMP WHERE last name LIKE '*a%’; 2Q: To write a query that performs an outer join of tables A and B and returns all rows from B, You need to write Any outer join (B) Left outer join (C) Right outer join (D) Cross join Inner join

Q3: If every non-key attribute is functionally dependent on the primary key, then the relation will be in 1NF (B) 2NF (C) 3NF (D) 4NF Q4: Match the following (a) Create (i) The E-R Model (b) Select (ii) Relationship Model (c) Rectangle (iii) DDL (d) Record (iv) DML Codes : (a) (b) (c) (d) (A) (iii) (iv) (i) (ii) (B) (iv) (iii) (ii) (i) (C) (iv) (iii) (i) (ii) (D) (iii) (iv) (ii) (i) 5Q: Which level of abstraction describes how data are stored in the database Physical level (B) View level (C) Logical level (D) Abstraction level 6Q: SQL command to delete a column from an existing table: Delete column (B) Drop table (C) Delete table (D) Alter table 7Q: An attribute or set of attributes within one relation that matches the candidate key of some (possibly the same) relation: Super Key (B) Candidate Key (C) Primary key (D) Foreign Key

Q8: If there exist Lossy decomposition between two tables then the tables may contain? a)Multivalued attribute b)Partial dependency c)Spurious tuples d)Transitive dependency Q9: Which of the following statement is true? a)A relation is in Third Normal Form then it will be always in BCNF. b)A relation is in Second normal form then it is not in first normal form c)A relation is in BCNF then it is in second normal form d)A relation is in second normal form then it contains partial dependency Q10: To write a query that performs an outer join of tables A and B and returns all rows from B, You need to write a. Any outer join b. Left outer join c. Right outer join d. Cross join e. Inner join

(a) If you were to assign a unique number or code to each album you owned in your record collection, this code would be considered a(n) _____. A) Entity B) Instance C) Associate D) Primary key (b) If you were collecting and storing information about your music collection, an album would be considered a(n) _____. A) Relation B) Entity C) Instance D) Attribute (c ) Which level of Abstraction describes how data are stored in the data base ? A. Physical level B. View level C. Abstraction level D. Logical level (d) The Primary key must be: A) NOT NULL B) unique C) option A and B D) option A or B (e) The number of attributes a relation consists is called …………. Of relation: A) association B) cardinality C) degree D) domain In data Independence we can Change the schema at one level A. Without having to change the schema at the next lower level B. Without having to change the schema at the next higher level C. Without having to change the schema at the top level D. Without having to change the schema at the bottom level

(a) The attribute AGE is calculated from DATE_OF_BIRTH . The attribute AGE is a) Single valued b) Multi valued c) Composite d) Derived (b) The attribute name could be structured as a attribute consisting of first name, middle initial, and last name . This type of attribute is called a) Simple attribute b) Composite attribute c) Multivalued attribute d) Derived attribute (c ) An entity set that does not have sufficient attributes to form a primary key is termed a __________ . a) Strong entity set b) Variant set c) Weak entity set d) Variable set (d) Which of the following is true ? A) A relation in BCNF is always in 3NF B) A relation in 3NF is always in BCNF C) BCNF and 3NF are same D) A relation in BCNF is not in 3NF (e) If no multivalued attributes exist and no partial dependencies exist in a relation, then the relation is in what normal form? A. First normal form B. Second normal form C. Third normal form D. Fourth normal form

(a) In relational operations the UNION of two sets (set A and Set B). This corresponds to a. A OR B b. A + B c. A AND B d. A - B (b) Which of the following SQL statements can calculate and return the absolute value of -33? A. SELECT ABS("-33") Absolute FROM DUAL; B. SELECT ABS('-33') "Absolute" FROM DUAL; C. SELECT ABS(-33) "Absolute" FROM DUAL; D. SELECT ABS(-33), Absolute FROM DUAL; (c) To write a query that performs an outer join of tables A and B and returns all rows from B, You need to write A. any outer join B. a left outer join C. a cross join D. a right outer join E. an inner join (d) In the INTERSECTION of two sets (set A and Set B). This corresponds to a. A OR B b. A + B c. A AND B d. A - B...