Assignment Objectives
Report is based on data and information management of the case study which was provided in the Assignment. It includes the Entity Relationship Diagram, introduction of MS Access database, different types of relationship in database and their cardinality, tables description and the business rules according to the case study, and the assumption which were required.
Database is the collection of some interrelated data or information of facts. And the accessing this data or information like retrieval, manipulation, updation, management is called Database Management System in short DBMS.
Microsoft Access is a database association structure from Microsoft that combines the social Microsoft Jet Database Engine with a graphical UI and programming progress contraptions. It is a man from the Microsoft Office suite of livelihoods, merged into the Professional and higher structures or sold independently. Microsoft Access is a Database Management System (DBMS) from Microsoft that merges the social Microsoft Jet Database Engine with a graphical UI and programming change contraptions. It is a bit of the Microsoft Office suite of employment, fused into the master and higher discharges.
Entity:
An Entity is and real world object which is represented by a rectangle in any entity relationship diagram, it has some attributes and one entity can be related to another entity with some relation and they are connected with connecting lines.
Relation:
Entities are somehow related to one another in a database, these relation are represented by diamond in the entity relation diagram, a relation is formed between two entities.
Attributes:
Every entity has some property itself, these properties are called attributes of that entity. Attributes are represented by ovals in any entity relationship diagram. A key attribute is the unique, distinguishing characteristic of the entity.
Cardinality:
Cardinality indicates what number of instances of a part identifies with one occasion of another substance. Ordinality is additionally steadily connected with cardinality. While cardinality chooses the events of a relationship, ordinality delineates the relationship as either required or discretionary. Around the day’s end, cardinality chooses the best number of affiliations and ordinality demonstrates the purpose of reality the base number of affiliations.
Tables description and business rules:
In this case, I went through the scenario provided, and i come to the tables according to the cases, here are the tables and their description attributes, their types, primary keys, foreign keys and other information about them.
course table contains the details related to the courses are offered by the Australian Institute of Music, it contains the course name, course duration and the course cost. First column is id which stores the unique id of the course, second column is the course which is type of the text which stores the name of the course available in the institute, third column is the duration which is type of number it stores the duration time of the course it contains whether 30 or 60 as it is given that any course run either for 30 minutes or 60 minutes, third column is the cost which is type currency which stores the cost of the specific course for the students to pay. It varies from course to course.
Required Tasks
id AutoNumber Primary Key
course text
duration Number
cost Currency
Staff table contains the details related to the staff members such as staff_id, staff name, staff’s family name, staff’s date of birth, staff’s phone no, staff’s residential address or where he/she use to live and the staff’s degree means staff members higher qualification. First column id the id which stores the unique id of the staff members, second column is the st_name which is type text which stores the name of the staff member in the ms access database, third column is the st_family_name which is type of text which stores the family name of the staff members, fourth column is the st_dob which is type of Date which stores the date of birth of the staff members into the ms access database, fifth column is the st_phone which is of type text which stores the phone no into the database of the staff member, sixth column is st_add which is of type text which stores the address of the staff members where they use to live in the city, seventh column is the st_degree which is of type text which stores the higher qualification of the staff members have, in general we call it degree so this column stores the higher qualification of the staff member as degree.
id AutoNumber Primary Key
staff_id Number
st_name text
st_family_name text
st_dob Date
st_phone text
st_add text
st_degree text
Student table stores all the information or details related all the students register to the university or music institute. A student has a name, unique id, address where he use to live, his / her date of birth, his / her phone no, and the date on which he joined the institute. In the Ms Access database student table has a column name id which is type AutoNumber it stores the unique id of every student, second column is the stud_name which is type of text which stores the name of the student in the table, third column is stud_add which is type of text which stores the address of the student where his residential is or where he use to live, stud_add stores the address of the student to the ms access database. Fourth column is the stud_dob which is type of Date which stores the date of birth of the student into the database, fifth column is the stud_phone which is of type text which stores the phone number of the student into the ms access database, sixth column is the stud_joining which is of type Date which stores the date of joining of the student into the institute, on which date he / she joined the institute the date of that day is stored into the table as stud_joining.
Implementation
id AutoNumber Primary Key
stud_name text
stud_add text
stud_dob Date
stud_phone text
stud_joining Date
Instrument table stores all the information about the instruments present in the institute like instrument name, manufacturer name and the number of the instrument of that type available at the institute at the present time. Instrument table has a column id which stores the unique id of that type of instruments which is Autonumber, second column is the instrument_name which is of type text which stores the name of the instrument into the ms access database, third column is manufacturer_name which is of type text which stores the name of the manufacturer who manufactured the instrument, fourth column is the quantity which is of type Number which stores the no of the instrument available at the institute.
Id AutoNumber Primary Key
instrument_name text
manufacturer _name text
Quantity Number
Course_enrollment table stores the details related to student enrollment procedure in the institute, it stores the details such as course_id, course_id, joining_date, duration, fee_paid, paid_date. course _enrollment table has a column id which stores the unique id every enrollment of the user to the course, second column is stud_id which is of type number which stores the id of the student who enrolled the course, third column is the course_id which is of type number which stores the id of the course which is enrolled by the student, fourth column of the table is the joining_date which is of type Date which stores the date of joining to the course of a student,
Fifth column of the table is duration which is of type number which stores the time duration of the course for that student who enrolled with that subject, sixth column of the table id fee_paid which is of type YES / NO which stores the information about the student whether he paid fee or not, seventh column of the table is paid _date which is of type date which stores the date on which student has to pay his fee for the enrolled for that course.
In this course_enrollment table stud_id and course_id are the foreign keys from the student table and the course table thus it means that the stud_id and the course_id in this course_enrollment table would belong to the student table and the course table.
Id AutoNumber Primary Key
stud_id Number Foreign Key (Student table)
Integrity Constraints
course_id Number Foreign Key(Course table)
Joining_date Date
duration Number
fee_paid Yes / No
paid_date Date
course_by_staff table stores the details about the relation between courses and the staff, which means that which course or subject is taught by which staff member/s or which staff member teaches the which courses. Here more than one staff members can teach a single subject or course and one teacher can teach more than one subject or course. In this table first column is id which is type of AutoNumber, second column is staff_id which is of type Number and is the foreign key of the staff table, it means that every staff_id from this table would belong to the staff table, third column of the table is course_id which is also type of Number and this the foreign key of the course table which means that that all the course_id from the course table would be from the course table.fourth column of this table is instrument_quantity which is of type Number, It stores the number of the instruments available at the college for that specific course.
id AutoNumber Primary Key
staff_id Number Foreign Key
course_id Number Foreign Key
instrument_quantity Number
Lessons table stores the details about the lessons which are scheduled in upcoming days, it includes course_id which course lesson it is and staff_id means which staff member is going to take that lessin and lesson_date on which date that lesson is going to be held. First column is id which is of AutoNumber type which stores the unique id of the lessons which are going to be held in future and the second column is course_id which is of Number type which is the Foreign key from the table course which means all the course_ids which are present in this table belong to the course table, it stores the course_id in the table which means which course lesson is going to be held. Third table is the staff_id which is of Number type which is also a Foreign key of table staff which means all the staff_ids in this table belong to the staff table, in this table it means which staff member is going to take that lesson of specific course, fourth column is the lesson_date which is of Date type which stores the date on which lesson is going to be held.
id AutoNumber Primary Key
course_id Number Foreign Key
staff_id Number Foreign Key
lesson_date Date
Lesson_attendance table stores the details about the attendance of the students on the lessons which are being held on their scheduled date, it stores the lesson_id from the lesson table and the stud_id from the student table to store the details about the attendance of the students for the lessons. First column is id which stores the id of the attendance which is of type AutoNumber, second column is lesson_id which stores the lesson_ids of the courses which is of Number type, third column is stud_id which is of the Number type which stores the student id in the table who were present at the lesson. Lesson_id is the Foreign key of the table lessons that means all the lesson ids in this table belong to the lesson table and the other hand stud_id is the foreign key of the student table which means all the stud_ids in this table belong to student table.
id AutoNumber Primary Key
lesson_id Number Foreign Key
stud_id Number Foreign Key
invoice table stores the details about the student who has been called for invoice, first column of this table is id which is of AutoNumber that stores the unique id of the invoice, the second column of this table is stud_id which is of type Number and the foreign from the table student table.
id AutoNumber Primary Key
stud_id Number Foreign Key
invoice _details table stores the information about the students who has the pending fee of the course and the due date and all, first column of this table is id which stores the unique id of every sub invoice detail, second column is invoice_id which is of Number Type which is foreign key from the invoice table, third column isdue_date which is of Date type which stores the due date for paying the fee, fourth column is course_id which is of Number type and foreign key from course table which stores the course which fee isn’t paid by the student yet, fifth column is amount which is of Number type which stores the amount to be paid by the student for that course.
id AutoNumber Primary Key
invoice_id Number Foreign Key
due_date Date
course_id Number Foreign Key
amount Number
- Lessons will be based on the courses which are available in the institute, lessons will have course_id, which will refer to the which course’s lesson is going to held, staff_id will refer to the staff member who will take the lesson of that course, lesson_date will refer to the date on which the lesson is scheduled. In short a lesson will have a course and a staff member who will take the lesson on a particular date.
- Lesson_attendance will have lesson id from the lesson it means many students can attend to one lesson and for that we will have to store their attendance lesson wise in the lesson_attendance table.
- A course can be taught by many staff members and a staff member can teach many courses, for that acourse_by_staff table is formed to store the information about the staff members and the courses.
- A student can enroll into multiple courses but an enrollment can be associated to only one student for that a course_enrollment table is formed which holds the every enrollment of the student to his / her enrolled courses and the other information related to like duration, fee_paid, paid_date etc.
- Invoice table store the students id who have unpaid fee for the courses, one student can enroll into multiple courses and the different courses may have different fee, due date , so if a student have any course fee unpaid then his entry will be in invoice table with a invoice id.
- If a student has a record in invoice table then it means he / she has one or more courses unpaid fee, now we have invoice detail table which have the invoice id from the invoice table, a student can have multiple courses with unpaid fee, here a invoice_id can have multiple record with other details of multiple courses related to one student.
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
