Relational DB Model

Question 1

Data Definition Language (DDL)

Answer 1

commands responsible for creating, editing and deleting SQL
tables

Question 2

DDL commands

Answer 2

used to define the structure, names, and types of
the data containers within a database

Question 3

Data Modeling Steps

Answer 3

1. Business Rules, Use cases
2. Conceptual Model
3. Logical Model

Question 4

What are business rules?

Answer 4

brief, precise, and unambiguous description of a policy, procedure, or
principle within a specific organization/lab/research area

this helps define entities, attributes, and constraints which distinguishes characteristics of data viewed by company/users

Question 5

What is functional dependency in DBMS?

Answer 5

describes the
relationship between attributes (columns) in a table

It shows that the value of one attribute determines the other.

So X → Y, where X is Determinant and Y is Dependent

EX: Student ID → Semester
This explains that a semester is dependent on a student ID which determines a specific semester.

Question 6

Primary key

Answer 6

candidate key chosen
by the DB designer

Question 7

candidate key

Answer 7

minimal superkey

Question 8

superkey

Answer 8

set of attributes that
uniquely determine all other attributes
in that relation

Question 9

foreign key

Answer 9

attribute whose values match the primary key values
in a related table

Question 10

required attribute

Answer 10

attribute that MUST have a value

Question 11

optional attribute

Answer 11

attribute that can be NULL

Question 12

Attribute Domain

Answer 12

set of possible values for a given attribute

Examples:
- Domain for the GPA: [0.0, 4.0]
- Domain for the gender: {Male, Female}
- Domain for the gene start location: positive integer numbers.

Question 13

Composite attribute

Answer 13

attribute
that can be further subdivided to yield additional attributes

Question 14

Simple attribute

Answer 14

attribute that cannot be subdivided

Question 15

Single-value attribute

Answer 15

only one valid
value can be assigned

Question 16

multi-value attribute

Answer 16

logically have more than one value – this usually occurs
with 1:M and M:N relationships but also with spreadsheet data

Question 17

derived attribute

Answer 17

attribute that’s computed/calculated from other attributes

Question 18

strong relationship (existence dependent)

Answer 18

Pros: you cannot
have a child entity without a
parent entity

Cons: composite keys are less
efficient and less flexible than
simple primary keys

Question 19

weak relationship (existence dependent)

Answer 19

Pros: Simple primary keys are more
efficient and more flexible than
composite primary keys

Cons: it is
possible to have a child entity
without a parent entity

Question 20

strong entity (PK)

Answer 20

Identifying relationship, when the PK of the related entity contains a PK
component of the parent entity

Question 21

weak entity (PK)

Answer 21

Non-identifying relationship, does not use the PK of the parent entity as
part of its own PK, but only as a FK

Question 22

transitive/indirect FD

Answer 22

dependent is indirectly dependent on
determinant

If a → b & b → c, then according
to axiom of transitivity, a → c

Ex: If enrol_no → dept and dept → building_no, then Enrol_no → building_no

Question 23

Why use FD?

Answer 23

removes redundancy, thereby improving data quality, accuracy and errors. - this is time and cost effective

helps identify poor db design

Question 24

non-trivial FD

Answer 24

dependent is strictly not a subset of the
determinant

X → Y and Y is not a
subset of X

EXs: roll_no → name, {roll_no, name} → age

Question 25

trivial FD

Answer 25

dependent
is always a subset of the determinant

X → Y and Y is the subset of X

EXs: {roll_no, name} → name, roll_no → roll_no