Consider below an entity relationship (ER) diagram: A TIF This is a ternary relationships T/F It...

 

Transcribed Image Text:

Consider below an entity relationship (ER) diagram: A TIF This is a ternary relationships T/F It is a one-to-one relationship. TIF The participation constraint is on B TIF The key constraint is on B. (13) (1b) (1c) (10) Observe below an instance of a relation Students S C 23 23 20 20 18 (10) (11) (1g) (1h) (11) (1J) (1K) (11) (1m) (10) In addition to relation Student S, consider an instance of another relation Dormitory D G (Sqft.) 220 180 300 B 23521 67562 Ann Bob Bob 89373 49274 Kate 47295 Jim TIF From this instance you could find that, for this relation of Student, there are 5 attributes and 4 columns TF Every relation must have one primary key (PK), for this instance, we can choose combination of (A. C) as the PK. (where we noticed A is already unique and not null) E 67562 47295 23521 49274 Dorm studio 520 89373 Dorm studio 915 TIF N TIF The combination of (B, C) can be chosen as the PK for this instance, TIF For attribute B and attribute C, each can be applied constraint not null and "unique" separately. TIF TIF For this instance, a clause "unique (B, D) is valid, but "primary key (B,D)" is not valid TIF B TIF D Y TIF N Y Dorm studio 101 Dorm studio 333 Dorm studio 518 250 330 We can set attribute E as foreign key that reference to attribute A in S, when we create D using DDL If a business rule says each student lives in exactly one studio and each studio accepts exactly one student, as a professional DBA to fully satisfy such special constraints, you will choose to create 2 tables to model it in SQL, instead of one table. You can use the set operation union, written as "SUD" in relational algebra expression, to combine two tables S and D. Cartesian product SXD always equal to natural join SD. and natural join always equal to full outer join Decomposing one table, which is resulted from "S 4 D, into two tables S and D according a normal form 3NF, is a process of normalization and satisfy 1NF. Consider below an entity relationship (ER) diagram: A TIF This is a ternary relationships T/F It is a one-to-one relationship. TIF The participation constraint is on B TIF The key constraint is on B. (13) (1b) (1c) (10) Observe below an instance of a relation Students S C 23 23 20 20 18 (10) (11) (1g) (1h) (11) (1J) (1K) (11) (1m) (10) In addition to relation Student S, consider an instance of another relation Dormitory D G (Sqft.) 220 180 300 B 23521 67562 Ann Bob Bob 89373 49274 Kate 47295 Jim TIF From this instance you could find that, for this relation of Student, there are 5 attributes and 4 columns TF Every relation must have one primary key (PK), for this instance, we can choose combination of (A. C) as the PK. (where we noticed A is already unique and not null) E 67562 47295 23521 49274 Dorm studio 520 89373 Dorm studio 915 TIF N TIF The combination of (B, C) can be chosen as the PK for this instance, TIF For attribute B and attribute C, each can be applied constraint not null and "unique" separately. TIF TIF For this instance, a clause "unique (B, D) is valid, but "primary key (B,D)" is not valid TIF B TIF D Y TIF N Y Dorm studio 101 Dorm studio 333 Dorm studio 518 250 330 We can set attribute E as foreign key that reference to attribute A in S, when we create D using DDL If a business rule says each student lives in exactly one studio and each studio accepts exactly one student, as a professional DBA to fully satisfy such special constraints, you will choose to create 2 tables to model it in SQL, instead of one table. You can use the set operation union, written as "SUD" in relational algebra expression, to combine two tables S and D. Cartesian product SXD always equal to natural join SD. and natural join always equal to full outer join Decomposing one table, which is resulted from "S 4 D, into two tables S and D according a normal form 3NF, is a process of normalization and satisfy 1NF.