Abc Assignment Help

SBM4102Data and Information Management

SBM4102 @ Data and Information Management

Applied   project

Due   date:
    Week 13 
Group/individual:
Group assignment
Word   count:
2500
Learning   outcomes:
[ULO1], [ULO2], [ULO3], [ULO4], [ULO5],    [ULO6], [ULO7]
Weighting:
60%

You must work in group (max 3 people) on this assignment.

Read the case study below please

Australian Institute of Music is a music college across the Australia. It provides wide ranges of courses related to music. The top level management of the college has recently decided to computerise the existing paper-based data to a database in order to keep record of all staff, students, courses, etc. Such database will facilitate saving, restoring, and reporting whenever required and reduced paper consumption in the college. The following describes information that is required to save in the database. 


Cost of each course is presented in the table below. 

Course
Cost
Singing
$100
Piano
$75
Vialon
$120
Guitar
$50
  • It should be noted that some courses run for half an hour while others run for an hour. The minimum course time to teach is 30 minutes.
  • The college also needs to maintain information about teaching staff. For example, it is required to record which staff teaches which instruments. For example, David teaches singing and piano. Information related to staff includes staff id, name and family, date of birth, phone number, address, and degree. Each staff has a unique ID to ensure easy identification. 
  • In addition, information related to students is required to maintain in this database. Students has name, postal address, date of birth, and contact phone number. Once a student is joined to the college, he or she can enrol in to interested instruments course. The date and time of the course(s) should be recorded, as well as the duration.
  • Furthermore, it is necessary to record which staff member will be taking the course and instrument the course is defined for. Students pay for their courses once the course is finished. It is necessary to record the condition for the course payment and the date paid.
  • Another type of information which is required to store is the available instruments in the college stock. This includes the instrument name, manufacture name, quantity or number of each instrument in the stock. The staff may use one or more instrument for teaching purpose. 
  • At the end of every month, the staff issues invoice statements. An invoice, which has a unique number, is issued for those students who have one or more unpaid courses in the previous month. An invoice, for example, contains date, student information, and total amount due.  

YOU NEED TO DO THE FOLLOWINGS


Part-1Entity-relationship data model

You are required to develop an ER model to represent the information requirements for given case study.  

 Your ER model must:

·show all necessary entities, attributes and relationships

·show unique identifiers

·show minimum and maximum cardinality.

·show associative entities, if applicable


Some business rules given in the case study may not be clear to you when you read the case study. You may simply make an assumption and then develop your ER model.

You are required to write brief definitions for the entities and attributes in your model.  For each entity and attribute in your model, write a sentence to describe the meaning of that entity or attribute.  

What you need to should submit

You should submit a Microsoft Word document containing:

  • A copy of your entity-relationship model.
  • A list of business rules that are apparent from the case study.
  • A list of any assumptions that you made with justifications.

Part-II Relational database implementation

You must implement the entity relationship model based on Assignment 1.

The recommended tool for this assignment is Microsoft Access.  

In implementing the ERD provided, you must complete the following tasks:

·Map the ERD into a set of relations in at least Third Normal Form (3NF).  You must ensure that your relations meet 3NF.  Show your working.

·List all the functional dependencies present in the relation

· 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 relationships as 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.

Data integrity

You are required to implement integrity constraints within the database to enforce the following requirements:  

For lessons:

·Lesson duration cannot be less than 30 minutes (half hour). 

·Cost cannot be negative (but it can be 0). 

·Paid must be either yes or no.

·Date paid cannot be in the future.

For invoice statements:

·Statement date must be supplied.

·Statement date cannot be in the future. 

·Paid must be either yes or no, it cannot be null. 

·Statement total must be supplied. 

·Statement total must be greater than 0.

·The payment date must be on or later than the statement date. 

Information requests

Create queriesmanually using SQL view.  

  1. List details of students who are under 18 years of age.  
  2. Show details of lessons coming up in the next 7 days (if any).
  3. 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.
  4. Show details of any student who has had piano lesson.

What you need to should submit

A Microsoft Access database

Tables with sample data

1.Relationships

2.Queries

3.Report.


Answer

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

1st 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

2nd 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

staff_id

staff_name

staff_family_name

staff_dob

staff_phone

staff_add

staff_degree

S01

PETER

NATALIA

27-02-1984

176253762

Queen Victoria Market MELBOURNE

DEPLOMA IN MSIC

S02

DELTA

MICHAEL

04-05-1981

231872771

The Crown, MELBOURNE

DEPLOMA

S03

JIMMY

MISSY

02-11-1980

134322121

Chinatown, MELBOURNE

DEGREE

S04

NICK

KATE

09-04-1976

128565899

Eureka Skydeck MELBOURNE

DEPLOMA


2) staff_course: staff_id, course_id

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

staff_id

course_id

S01

C01

S01

C04

S02

C01

S02

C03

S03

C04

S04

C02


3) course: course_id, course_name, cost, time

It’s Primary Key:  course_id

course_id

course_name

cost

time

C01

Singing

100

60

C02

Piano

75

60

C03

Vialon

120

60

C04

Guitar

50

60


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)

instrument_id

instrument_name

course_id

stock_qty

manufature_name

I01

PIANO

C02

3

YAMAHA

I02

VIOLIN

C03

4

ROCKY

I03

Guitar

C04

5

TAYLOR

I04

AMPLIFIER KIOSK

C04

2

STAR


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

It’s Primary Key:  s_id

s_id

s_name

s_add

s_dob

s_phone

s_family_name

s_email

ST0001

MICKY

MELBOURNE

08-02-1997

298435667

MICHAEL

MICKY@xyz.com

ST0002

ANNA

STREET NO - 75 MELBOURNE

30-03-2001

273645789

PETER

ANNA@xyz.com

ST0003

LISA

STREET NO - 105 MELBOURNE

15-12-2000

243654167

KATE

LISA@xyz.com

ST0004

KAITE

STREET NO - 11MELBOURNE

22-06-2000

277677899

NICK

KAITE@xyz.com


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)

s_id

course_id

datetime

course_duration

staff_id

payment

date_paid

ST0002

C01

28-07-2018

60

S01

Y

11-09-2018

ST0001

C01

25-07-2018

60

S02

Y

02-09-2018

ST0001

C04

08-08-2018

30

S03

Y

10-09-2018

ST0003

C02

18-09-2018

60

S04

N

10-09-2018


7) invoice: invoice_no, s_id, amount, date

       It’s Primary Key:  invoice_no

Foreign Key (s_id) References student(s_id)

invoice_no

s_id

amount

Date

IN0001

ST0001

150

31-08-2018

IN0001

ST0002

100

31-08-2018

IN0001

ST0001

120

30-08-2018

IN0001

ST0002

110

30-08-2018


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.  


SELECT student.s_dob as   Date_Of_Birth, Year(Now())-Year([s_dob]) AS Age
FROM student
WHERE   Year(Now())-Year([s_dob])<=18;


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


SELECT a.course_id as Course_ID,   b.course_name as Course_Name, b.time
FROM student_course AS a, course   AS b
WHERE   DateDiff('d',now(),a.datetime)<8 And b.course_id=a.course_id;


  • 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.


SELECT a.s_id as Student_ID, b.s_name as Name, b.s_add as   Addess, b.s_dob as Date_Of_Birth
FROM student_course AS a, student AS b
WHERE a.course_id =(select course_id from course where   course_name='PIANO') 
and a.s_id=b.s_id;


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


SELECT a.s_id,   a.s_name, a.s_family_name (select count(*) from student_course b where b.s_id   =a.s_id)
FROM student AS a;


Report.

1) 

2) 

3) 




Customer Testimonials