SBM Data and Information Management

Part-1:  Entity-relationship data model

ER model is a graphical representation of entities and their relationship to each other, typically used in computing in regard to the organization of data within database. The meaning is described in terms of a conceptual or ER schema. ER schemas are comparable  class diagrams in UML

List of entities

Entity: An object in the world that can be distinguished from other objects

Entity set: A set of similar entities

Here the following entities are:

• staff
• staff_course
• course
• instrument
• student
• student_course
• invoice

List of attributes

The basic unit of information about any entity occurrence. Here describe the list of attributes i.e;

• staff: staff_id, staff_name, staff_family_name, staff_dob, staff_phone, staff_add, staff_degree
• staff_course: staff_id, course_id
• course: course_id, course_name, cost, time
• instrument: instrument_id, instrument_name, course_id, stock_qty, manufature_name
• student: s_id, s_name, s_add, s_dob, s_phone, s_family_name, s_email
• student_course: s_id, course_id, datetime, course_duration, staff_id, payment, date_paid
• invoice: invoice_no, s_id, amount, date

Business Rules:

Business rules are the constraints that you wish to impose protect the database from becoming incomplete, inaccurate, or inconsistent. Although you may not be able to implement some business rules within the DBMS. It is concerned only with high-level design that is, specifying what business rules are required irrespective of how this might be achieved. Having identified the business rules, you will have a logical data model that is a complete and accurate representation of the organization to be supported by the database. 

We consider the following types of business rules i.e, required data, column domain constraints, entity integrity, multiplicity, referential integrity, other business rules.

Here the business rule defined by the database i.e

1. The minimum course time to teach is 30 minutes.
2. The Maximum Course time to teach is 1 hr.
3. Invoice will generate only after course is finished for the student.
4. Invoice will be generated only for previous month completed course. 

Assumptions:

1. Student(s) cannot enrol is same course again if the last payment is due.

Part-II Relational database implementation

Normalization: Normalization is a formal process for design which attributes should be grouped together in a relation. Normalization can be a conventional method regarding design which attributes needs to be grouped together in a relation. Just before proceeding with all the physical design we'd like a strategy to confirm the particular logical design to this point. Normalization is a tool to be able to confirm & enhance the logical design. It requires to fulfil a few constrain.

There are several normal forms defined,

First Normal Form (1NF)

Second Normal Form (2NF)

Third Normal Form (3NF)

Boyce codd normal form (BCNF)

Fourth Normal Form (4NF)

Fifth Normal Form (5NF)

First Normal Form:

 First Normal Form eliminates repeating groups by putting each into a separate table and connecting them with a one-to-many relationship.

Two rules follow this definition:

1. Each table has a primary Key made of one or several fields and uniquely identifying each record
2. Each field is atomic, it does not contain more than one value.

Second Normal Form: 

Second Normal Form eliminates functional dependencies on a partial key by putting the fields in a separate table from those that are dependent on the whole key.

Third Normal Form:  

Third Normal Form eliminates functional dependencies on non-key fields by putting them in a separate table. At this stage, all non-key fields are dependent on the key, the whole key and nothing but the key.

Here the table are fully normalised i.e,

1. staff (staff_id, staff_name, staff_family_name, staff_dob, staff_phone, staff_add, staff_degree) 

It’s Primary Key: staff_id

1.  staff_course (staff_id, course_id) 

Foreign Key (staff_id) References staff(staff_id), Foreign Key (course_id) References course(course_id) 

1.  course(course_id, course_name, cost, time)

It’s Primary Key:  course_id

1. Instrument (instrument_id, instrument_name, course_id, stock_qty, manufature_name       It’s Primary Key:  instrument_id) 

Foreign Key (course_id) References course(course_id)

1. Student ( s_id, s_name, s_add, s_dob, s_phone, s_family_name, s_email)

It’s Primary Key:  s_id

1. student_course: s_id, course_id, datetime, course_duration, staff_id, payment, date_paid

Foreign Key (course_id) References course(course_id), Foreign Key (s_id) References student(s_id)

1. invoice (invoice_no, s_id, amount, date)

It’s Primary Key:  invoice_no, Foreign Key (s_id) References student(s_id)

Functional Dependencies

A functional dependency (FD) is a constraint between two sets of attributes in a relation from a database.

                       FD: X → Y is called trivial if Y is a subset of X.

zip à city, state

1. Multi-valued Dependencies

Multivalued dependency  is a full constraint between two sets of attributes in a relation.

xàà y

s_name à s_phone

s_name à s_id

1. Candidate Keys

A candidate key is an attributes or set of attributes that uniquely identifies individual occurrences or an entity type. It is always NOT NULL and unique, which means that the values in theses column(s) must never change.

staffl: {staff_id, staff_phone}

1.  4NF Violators 

add à city, state

1.  Decomposition

1^st Decomposition

Decomposition on xàày    or  s_nameà s_phone,  s_nameà s_id

Then r(x)  or  student(s_name,  s_phone, s_id)

New Candidate Keys
student: { s_id, s_name, s_phone, s_email}

Remaining 4NF Violators
s_add à city, state

2^nd Decomposition

Decomposition on xàày   or   s_phoneà s_name, s_phone à s_email
Then r(x)  or  student(s_name,  s_phone, s_email)

New Candidate Keys
student: { s_id,  s_phone, s_email}

Remaining 4NF Violators
add à city, state

Final Decomposition

student: { s_id,  s_phone, s_email}

Tables with sample data:

Relational database store in tables. Defined by a collection of columns and contain zero or more rows. Tables typically represent a type of object or entity. Here describe the table entities and attributes with its primary and foreign key with data.

Primary Key: 

• Primary key cannot accept null values
• By default, primary key is clustered index and data in the database table is physically organized in the sequence of clustered index.
• We can have only one primary key in a table
• Primary key can be made foreign key into another table.

Foreign Key: 

• Foreign key can accept multiple null values.
• More than one foreign key in a table.
• Foreign key is a field in the table that is primary key in another table

1) staff: staff_id, staff_name, staff_family_name, staff_dob, staff_phone, staff_add, staff_degree

Primary Key: staff_id

2) staff_course: staff_id, course_id

Foreign Key (staff_id) References staff(staff_id), Foreign Key (course_id) References course(course_id) 

3) course: course_id, course_name, cost, time

It’s Primary Key:  course_id

4) instrument: instrument_id, instrument_name, course_id, stock_qty, manufature_name       It’s Primary Key:  instrument_id

Foreign Key (course_id) References course(course_id)

5) student: s_id, s_name, s_add, s_dob, s_phone, s_family_name, s_email

It’s Primary Key:  s_id

6) student_course: s_id, course_id, datetime, course_duration, staff_id, payment, date_paid

Foreign Key (course_id) References course(course_id), Foreign Key (s_id) References student(s_id)

7) invoice: invoice_no, s_id, amount, date

       It’s Primary Key:  invoice_no

Foreign Key (s_id) References student(s_id)

Relationships

• They represent logical links between two or more entities.
• There are three types of relationship i.e. 

One to One Relationship,

One to many Relationship

Many to Many Relationship

One to one relationship

• One to one relationship set between course entity and instrument entity sets.

One to many relationship 

• One to many relationship set between course staff and staff_course entity sets.
• One to many relationship set between course and staff_course entity sets.
• One to many relationship set between student and student_course entity sets.
• One to many relationship set between student and invoice entity sets.
• One to many relationship set between course and student_course entity sets.

Many to many relationship

• Many to many relationship set between staff_course and student_course entity sets.

Queries

• List details of students who are under 18 years of age.  

• Show details of lessons  coming up in the next 7 days (if any).

• Show a list of students and the total number of lessons they have had.  Show the student ID, family name, given name, and the number of lessons.

• Show  details of any student who has had piano lesson.

Report.

1) 

2) 

3)