18CSC303J - Database Management System UNIT 4 & 5 - 4 MARKS

TOTALLY THERE WERE 10 4M GIVEN. 5 FROM SET A  AND 5 FROM SET B. I HAVE TAKEN 4 FROM EACH SET

 4M:


1) Design goals of relational database design & 6) paraphrase the pitfalls occurring in relational database design

Relational database design requires that we find a ‘good’ collection of relation schemas. A bad design may lead to

  • Repetition of information

  • inability to represent certain information

Design goals:

  • Avoid redundant data

  • Ensure that relationships among attributes are represented

  • Facilitate the checking of updates for violation of database integrity constraints

Redundancy:

  • Wastes space

  • Complicates updating, introducing possibility of inconsistency

Null values

  • Can use, but difficult to handle

  • Cannot store information if value does not exist


2) attribute closure of functional dependency with example

  • The closure of functional dependency means the complete set of all possible attributes that can be functionally derived from given functional dependency using the inference rules known as armstrong’s rules

  • Attribute closure of an attribute set can be defined as set of attributes which can be functionally determined from it


Using FD set of the table, attribute closure can be determined as:

(STUD_NO)+= {STUD_NO, STUD_NAME, STUD_PHONE, STUD_STATE, STUD_COUNTRY, STUD_AGE}


(STUD_STATE)+= {STUD_STATE, STUD_COUNTRY}


3) Any two dependencies in relation to the normal forms

In third normal form, you should not have transitive dependency, so you can write that

In second normal form, you should not have partial dependency, so you can write that

And functional dependency is kinda used in all the normal forms, so choose any two of the three and write


The answer is in (6) 12M.


5) What is the purpose of using undo and redo operations in transactions?

Undo:

  • Undo of a log record <Ti, X, V1, V2> writes the old value V1 to X

  • Undo restores the value of all data items updated by Ti to their old values, going backwards from the last log

Redo:

  • Redo of a log record <Ti, X, V1, V2> writes the new value V2 to X

  • Undo sets the value of all data items updated by Ti to their new values, going forward from the first log



7) differentiate between trivial and non-trivial functional dependencies

Trivial dependency:

  • It occurs when B is a subset of A

  • A -> B

  • { DeptId, DeptName } -> DeptId

  • The above is a trivial functional dependency since DeptId is a subset of DeptId and DeptName

Non Trivial Dependency:

  • It occurs when B is not a subset of A

  • A -> B

  • { DeptId } -> DeptName

  • The above is a non trivial functional dependency since DeptName is not a subset of DeptId


8) Discuss about BCNF

  • Boyce and Codd Normal Form is a higher version of the third normal form

  • This form deals with certain type of anomaly that is not handled by 3NF

  • A 3NF table which does not have multiple overlapping candidate keys is said to be in BCNF

  • Following conditions for BCNF:

  • It must be in 3rd normal form

  • For each functional dependency ( X -> Y), X should be a super key and it is a trivial functional dependency


10) Explain the compatibility matrix for a transaction by using shared lock and exclusive lock

  • A lock is a mechanism to control concurrent access to a data item

  • Data items can be locked in two modes:

  • Exclusive (X) mode: Data item can be both read as well as written

  • Shared (S) mode: Data item can only be read

Lock compatibility matrix:

  • Any number of transactions can hold shared locks

  • But if any one transaction has X lock then no other transaction has any lock

Comments

Post a Comment

Popular posts from this blog

18CSC305J - Artificial Intelligence UNIT 4 & 5

Image
 I have only updated some answers...The ppt is not clear and many contents are missing, so you can say we have to refer online only for the answers. So please make do with these. 4M: Supervised vs Unsupervised SUPERVISED LEARNING UNSUPERVISED LEARNING Requires labeled data Uses unlabeled data Predicts output based on input-output relationships Extracts patterns or relationships from the input data Classification and regression Clustering and association Receives feedback during training to adjust parameters Does not receive explicit feedback during training More complex More simple What is adaptive network? Adaptive learning brings human analysts into the process at every step. This is in contrast to rule-based, simple machine learning and deep learning approaches, where the humans only create rules and label data at the start of the process.  Adaptive learning systems require the least human effort because they only require human input when it matters most and continually exp...
Read more

18CSC303J - Database Management System UNIT 4 & 5 - 12 MARKS

Image
12M: 2) 2NF and 3NF with examples Second Normal Form (2NF): For a table to be in the Second Normal Form, It should be in the first normal form It should not have partial dependency 1 column primary key (Basically no 2 rows have the same primary keys) Example: Here the course code is unique and can be taken as primary key SUBJECT NAME COURSE CODE DBMS CS101 CD CS152 AI CS154 Storing student enrollment in various courses. Here, both the columns are not unique, but the tuple (student name, course code) is unique since a student cannot enroll in the same course more than once). But this is not the second normal form. STUDENT NAME COURSE CODE A CS152 B CS101 A CS154 C CS101 To convert to 2NF, we need to break it into 2 tables. In the first table, the second column is unique and we can attach each of these with the course codes in the second table STUDENT NAME ENROLLMENT NUMBER A 1 B 2 C 3 COURSE CODE ENROLLMENT NUMBER CS101 2 CS101 3 CS152 1 CS154 1 These 2 tables together provide u...
Read more