New! Hire Essay Assignment Writer Online and Get Flat 20% Discount!!Order Now

SMB4102 Data and Information Management

Published : 10-Sep,2021  |  Views : 10

Question:

  • Create all the relations in a Microsoft Access database.  Consider each attribute in every table and make appropriate choices regarding data types and sizes, indexes, required/not required, and validation rules.  Your choices should be appropriate for each attribute and should support data integrity.  
  • Create relationshipsas appropriate.  Enforce referential integrity for all appropriate relationships in the database.   
  • Populate the database with sample data.  You must include sufficient sample data to test your queries and report(s).  You should aim for at least 4-5 rows in every table.

Answer:

The business rules derived from the case study are;

  • A teaching staff can teach one or more classes in which the teaching staff is qualified.
  • A student enrolls in one or more courses in a semester.
  • A course has many lessons taught by one teaching staff.
  • A teaching staff can use one or more instruments in a lesson.
  • Students are supposed to pay for every course they enroll in once the course is over.
  • The college has many instruments where by each instrument has a specified quantity in stock.
  • An invoice is issued to those students who have one or more unpaid courses in the previous month.

The following assumptions are made on top of the derived business rules;

  • A student cannot make partial payments for a course. This is because payment for a course is recorded once in the payments table.
  • Every lesson has its own duration but the duration must either be 30 minutes or one hour.
  • A teaching staff can be an expert in many courses. This is because a teaching staff can teach one or more courses.
  • Every course is taught by one teaching staff every semester. No course is taught by two teaching staff at any one point.

2 Relational Database implementation

2.1 Normalization

Normalization occurs in 3 steps

  • 1stNormal form- At this level, elimination of repeating groups and definition of a primary key attribute in every relation is done.
  • 2ND normal form- At this level, all partial dependencies in each relation are eliminated for the relations derived in 1NF.
  • 3rdNormal form- At this level, all transitive dependencies are eliminated for all the relations gotten in 2NF. This makes all non-key attributes in every relationship to be fully dependent on a key attribute for every relation. At this level, the relations are stable enough to implemented as tables to form a complete database.

Following the three levels of normalization, the following relations are derived from the Entity Relationship Diagram.

Course (courseID,name,cost)

Teaching_staff (staffID,name,family,dob,phone,address,degree)

Staff_courses (staffCourseID,staffID,courseID)

Instruments (instrumentID,name,manufacturer_name,quantity)

Lesson_instruments (lesson_instr_ID,lessonID,instrumentID)

Lessons (lessonID, coursed, duration, date)

Students (studentID,name,postalAddress,dob,contactNO)

Payments (paymentID,studentID,couseID,status,datePaid)

Course_enrollment (enrollmentID, studentID, courseID)

All relations are in 3NF because no transitive dependencies exist in any relation. This can be shown by showing the functional dependency in each relation where every non-key attribute depends on a key attribute or on the primary key of the relation

2.2 Functional dependency

All relations have functional dependencies existing between the attributes.

(coursed)à(name,cost)

(staffID)à(name,family,dob,phone,address,degree)

Staff_courses relation

(staffCourseID)à(staffID,courseID)

instruments relation

(instrumentID)à(name,manufacturer_name,quantity)

Lesson_instruments relation

(lesson_instr_ID)à(lessonID,instrumentID)

Lessons relation

(lessonID)à(coursed, duration, date)

students relation

(studentID)à(name,postalAddress,dob,contactNO)

payments relation

(paymentID)à(studentID,couseID,status,datePaid)

Enrollment relation

(enrollmentID)à(studentID, courseID)

From the set of functional dependencies shown above all the relations are in 3NF because there is no transitive dependency present in any relation.

3 References

Czenky, M. (2010). Normalization based on dependency diagram. Teaching Maths and Computer Science, 1-121. Retrieved May 3, 2017, from http://tmcs.math.unideb.hu/load_doc.php?p=188&t=doc

Watt, A. (n.d.). Database Design:Chapter 11 Functional Dependencies. Retrieved May 3, 2017, from https://opentextbc.ca/dbdesign/chapter/chapter-11-functional-dependencies/

Watt, A. (n.d.). Database Design:Chapter 12 Normalization. Retrieved May 3, 2017, from https://opentextbc.ca/dbdesign/chapter/chapter-12-Normalization/

Get An Awesome Price Quote For Your Paper – Absolutely FREE!
    Add File
    Files Missing!

    Please upload all relevant files for quick & complete assistance.

    Our Amazing Features

    delivery

    No missing deadline risk

    No matter how close the deadline is, you will find quick solutions for your urgent assignments.

    work

    100% Plagiarism-free content

    All assessments are written by experts based on research and credible sources. It also quality-approved by editors and proofreaders.

    time

    500+ subject matter experts

    Our team consists of writers and PhD scholars with profound knowledge in their subject of study and deliver A+ quality solution.

    subject

    Covers all subjects

    We offer academic help services for a wide array of subjects.

    price

    Pocket-friendly rate

    We care about our students and guarantee the best price in the market to help them avail top academic services that fit any budget.

    Getting started with MyEssayAssignmentHelp is FREE

    15,000+ happy customers and counting!

    Rated 4.7/5 based on
    1491 reviews
    ;