Database Design, Business Rules, and Normalization Assignment

This assignment solution addresses various database concepts, encompassing business rules, data modeling, and normalization techniques. The solution begins by explicitly stating business rules for a university database, including relationships between colleges, terms, courses, and instructors, as well as implicit rules regarding unique course names and instructor-term combinations. It extends to include a student module and a client-agent-policy relationship. The solution then explores missing business rules, identifies weak and strong entities, and discusses unique and derived attributes, and various relationship cardinalities. The solution further covers the use of inheritance, entity subtypes, and their relationships, and delves into relational database concepts like tuples, attributes, and relations. It also addresses stored versus derived attributes, and explores candidate keys, superkeys, and primary keys. Finally, the assignment tackles functional dependencies, normalization, and the design of database schemas, including the identification of prime and non-prime attributes, and the application of normalization rules to achieve database efficiency.

Chapter - 2
25(a). Business Rules Explicitly stated
1. A university have more than one college.
2. Each college has 4 terms.
3. Each term can have one or more than one course.
4. Instructor can teaches one or more than one course.
Business Rules Implicitly indicated
1. Course# and course name should be unique.
2. Instructor teaches in 0 to 4 terms.
3. In a term the combination of instructor and course should be unique.
25(b). To complete this data model there should be module of student,
which contain studentID, student name, student address and college that is
being chosen by him.
26. There are two users that are client and agent and relation between
them is policy. A client buys the policy from the agent and can buy many
policy with different agent and relation between client and policy is one to
many. A agent can sell the policy to a client and can sell many policy and
relation between aget and policy is zero to many that an agent is related to
any policy is not mandatory

.

Chapter - 3
6. The business rule that is missing is the the customer module. There
should be entity of customer who can book a reservation. A customer can
book a flight reservation but the combination of reservation and flight for a
particular customer should be unique.
7. (a) Weak Entity - Service
Strong Entity - AIRCRAFT, TECHNICIAN, TRUCK, FLIGHT,
RESERVATION
Unique identifier of entity
AIRCRAFT - Tail
TECHNICIAN - SS
TRUCK - License Plate
FLIGHT - FlightID
RESERVATION - Reserv ID
(b) Partial key - Tail in entity AIRCRAFT.
(c) Optional attributes
Hours in Entity SERVICE
Tenure in Entity TECHNICIAN
Multi- valued attribute
Speciality in entity TECHNICIAN
(d) Derived attributes -
SERVICE is derived from AIRCRAFT
RESERVATION is derived from flight
(e) Recursive relationship is in FLIGHT entity
Binary relationship is between FLIGHT and RESERVATION,
And SERVICE and TECHNICIAN

(f) Partial participations among entities are
AIRCRAFT and SERVICE
FLIGHT and RESERVATION
Total participation among entities are
AIRCRAFT and FLIGHT
SERVICE and TECHNICIAN
TECHNICIAN and TRUCK
(g) 1:1 cardinality ratio means there should be unique foreign key in
both entity
1:n cardinality ratio means in one entity there should be unique
foreign key and in other there can be repetition of foreign key.
N:m cardinality ratio means there can be many number of foreign key
in both entity.
(h) Diamond has a single line, the relationship only includes strong
entity sets.
Diamond has double lines, the relationship includes a weak entity set.
(j) It is the optional clause.

17.
18.

Chapter 4
13. (a) PERSON, GROUND_STAFF AND STOCKHOLDER
(b) STOCKHOLDER - MUTUAL_FUND, BANK, INDIVIDUAL
PERSON - EMPLOYEE, PASSENGER
GROUND_STAFF - TECHNICIAN, RESV_AGENT, GATE_STAFF
FLIGHT_STAFF - PILOT, FLIGHT_ATTENDENT
(c) EMPLOYEE AND INDIVIDUAL are shared subclass.
(d) STOCKHOLDER - MUTUAL_FUND, BANK, INDIVIDUAL
PERSON - EMPLOYEE, PASSENGER
GROUND_STAFF - TECHNICIAN, RESV_AGENT, GATE_STAFF

FLIGHT_STAFF - PILOT, FLIGHT_ATTENDENT
(e) PERSON
(f) 6
(g) 5
(h) PASSENGER
(i) Those entity who are mutually coupled comes under the arc.
(j) GROUND_STAFF
(k) STOCKHOLDER, PERSON
(l) RESV_AGENT, FLIGHT_ATTENDENT
(m) No. The optional mark should be removed.
(n) STOCKHOLDER
(o) EMPLOYEE
(p) Company_name
(q) PILOT
(r) PERSON
(s) Yes it is possible because individual stockholder can be a person.
17.

Chapter 6
1. Tuple is data that a table contain
Attributes are the column name of the table.
Relation of table consist of its column and rows.
3. The stored attribute are such attributes which are already stored in the
database and from which the value of another attribute is derived is called
stored attribute.
The derived attributes are such attributes for which the value is derived or
calculated from stored attributes.
5. If two entities of database structure are related and they are not equal
then they are subset.
If two entities of database structure are related and they are equal then
they are proper subset.

6. A candidate key is a column, or set of columns, in a table that can
uniquely identify any database record without referring to any other data.
super key is the combination of fields by which the row is uniquely identified
and the candidate key is the minimal super key. Basically, a Candidate Key
is a Superkey from which no more Attribute can be pruned.
7.A primary key is a special relational database table column (or
combination of columns) designated to uniquely identify all table records.
A Candidate Key can be any column or a combination of columns that can
qualify as unique key in database. There can be multiple Candidate Keys in
one table. Each Candidate Key can qualify as Primary Key
8. Primary Key - Student Number
Candidate Key - Student Number
SuperKey - Student Number and Class Name
10. (a)
30 A 20
45 B 32
75 A 24
(b)
45 B 32
(c)
30 B 24
(d)
30 A 20
75 C 12
Chapter - 7

5. It does not cover minimal functional dependency.
CarID CarModel CarCylinder CarOrigin TaxID
9. The advantage to candidate key is
1. The relation does not have two distinct tuples (i.e. rows or records in
common database language) with the same values for these
attributes (which means that the set of attributes is a superkey).
2. There is no proper subset of these attributes for which (1) holds
(which means that the set is minimal).
11. (a) Prime Attributes – Attribute set that belongs to any candidate key
are called Prime Attributes.
Non Prime Attribute – Attribute set does not belongs to any candidate key
are called Non Prime Attributes.
(b) A non-key attribute does not uniquely identify an instance of an entity.
For example, a database may have multiple instances of the same
customer name, which means that "customer-name" is not unique and
would probably be a non-key attribute.
15. (a) UNI (Client, Office, Stock, Broker, Profile, Company, Risk_profile,
Analyst, Investment, Volume, Commision, Return, Account, Assets)
(b) Yes it covers minimal cover of F.
(c) Candidate Keys - Client and Company
(d) Primary key should be Client and it is unique always.
Chapter - 8.
8. (a)

CarID CarModel CarCylinder CarOrigin TaxID
(b) Candidate Key is CarModel, CarOrigin
(c) Primary Key CarID
(d) Based on my class the field CarOrigin violet the normal form rule, there
should be another table of origin and pass the reference of that in car table.
(e)
CarID CarModelID CarCylinder CarOriginID TaxID
18. (a) Student and Subject composite will define its Teacher
Teacher teaches Subject
Subject is not bound with particular Teacher
Student and Teacher composite will define its Subject
(b) The following data is consistent they follow all the functionality
dependent rule.
(c) The third field contain student as john subject as math and teacher as
white is violating the BCNF.