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.
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
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.
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.
(lessonID)à(coursed, duration, date)
From the set of functional dependencies shown above all the relations are in 3NF because there is no transitive dependency present in any relation.
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/
Our Amazing Features
No missing deadline risk
No matter how close the deadline is, you will find quick solutions for your urgent assignments.
100% Plagiarism-free content
All assessments are written by experts based on research and credible sources. It also quality-approved by editors and proofreaders.
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.
Covers all subjects
We offer academic help services for a wide array of subjects.
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