代写SIT103、代做Database留学生、代写SQL程序语言、代做SQL

日期：2019-05-18 02:19

SIT103 – Data and Information Management

Assignment 2 Due Date - 11.59 PM on 17 May 2019

Database for Movie Lover Company

Total mark of the assignment: 100 (20% of the final unit assessment).

Late Submission and Extension Policy

All students MUST submit an assignment on the due date, whether it is completed or not.

Students who have just cause may email your lecturer and apply for an extension BEFORE the due date

unless they are hospitalised or there are extenuating circumstances that prevent this. All applications

must be accompanied by documentary evidence of the disadvantage causing this request.

Late Assignments will be penalised by 5% for each day late. After one week, assignments will

not be marked.

Unit Learning Outcomes (ULOs)

Of the three Unit Learning Outcomes (ULOs) of this unit, this assignment will focus on the

last two ULOs. These are:

ULO 2 - At the end of this unit students will be able to evaluate data models and apply data

modelling techniques to capture the data aspects of real-world situations.

ULO 3 - At the end of this unit students will be able to design and develop relational

databases by using SQL and a database management system.

The assessment of this assignment will indicate whether students can partially attain these

unit learning outcomes.

Scenario

A company named MovieLover provides the service of renting out movie videos to its members.

Currently the company has about 200 staff working in 25 branches. Each staff member works at one

branch. Each branch is managed by a manager who is also a staff member. When a staff member

joins the company, the staff member’s personal information is recorded, which includes staff

number, name, position (e.g. manager, assistant), annual salary, email address and branch number.

MovieLover stocks about 10,000 movie titles. Each movie title is uniquely identified by a catalog

number, and has its genre (e.g. romance, action, science-fiction etc.) and daily rental rate (i.e., rental

price). In most cases, each movie title has many copies at each branch, and each copy is identified

by a movie copy number. If a copy is rented out, it is not available for renting until it is returned.

In order to rent movies, a customer is required to be registered as a member of MovieLover by

providing his/her personal information, which includes name, postal address, email address, phone

number, password, branch number and registered date. Email address and password are required

for a member to access his/her account at MovieLover website.

To assist members in selecting movies, MovieLover website provides a movie search function

supported by the database system. With the search function, a member can search for preferred

movies based on the movie title, main actor, director, genre, or release date.

A member rents movie copies from a branch. The company keeps the rental history for each

member. The history records keep the member’s ID number, rented movies, renting out dates,

returning dates, overdue or not, and fined or not.

As a database expert, you are approached by MovieLover CEO to analyze current management and

business processes of the company, design and implement a database to enable the staff members

and customers to easily obtain required information for better management and services.

Answer following questions.

1. Identify business rules. Business rules are important to define relevant constraint in

the organisations. Some organisations have specific rules only practice by the

organisation. (e.g. A business rule for a car sales company may be “for each car

separate invoice should be issued.”). You can make any appropriate assumptions and

mention them in your answer.

(5 marks)

2. ER diagram is based on the business rules, and should clearly label all entities, entity

attributes, primary and foreign keys, relationship and connectivity. The cardinality is

optional.

Instruction: Use professional software (e.g., MS Office Visio) to draw the ER

diagram. Crow’s Foot notation is preferable.

(40 marks)

3. Develop relational schemas. Relational schemas should be derived using the ERD.

You should map cardinalities correctly from ERD to Relational Schema. You should

clearly indicate the referential integrity constraints (primary and foreign key

relationships) using arrows. Clearly indicate datatype for each attribute.

e.g.

Emp( eid: integer, ename: string(50), address: string(100), did: number)

Dept(did: number, dname : string (15))

(15 marks)

4. SQL commands

1. Create all tables in Deakin Oracle DBMS (about nine tables including composite

tables) and Populate the tables with sample data (10 records in each table is

recommended).

2. Alter the staff table and add new field Phone Number for staff table. Type for

Phone Number should be number.

3. Increase the annual salary for all staff member by 2%.

4. For a given actor first name, display all movies he/she played a role in

5. Display the rental history of a given customer (identified by member ID number),

showing customer name, phone number, movie copy number, movie title, branch

number, renting out date, and returning date.

6. Create your own query. It must include a nested query. Submit the following:

i question your query is answering the SQL query

ii the mark for this question will depend on the complexity of the query.

iii higher marks will be given for queries that are more complex and/or innovative.

iv if you do not provide a description of what question the query is answering, you

will get zero for this query. (40 marks)

Submission: Submit a copy of your solution – a single document (word or pdf format – 5 marks of

penalty will be applied for other format of submissions), electronically on the course portal, on or

before the due date.

You must submit your Report with a

Cover page (with student number, name and a signed statement that mentioned all

the work is your own work.)- Failing of one of the items will applied for 5 marks of

penalty form the final mark.

Answers for above mentioned questions. The design should be very clearly

documented. And answers should be written in the order of the questions. If not

5 marks of penalty will be applied.

Simple Example on how to submit Q4

Screen screenshots (Please provide clear screen shot showing your work)

Q1 – Create employee table

CREATE TABLE EMPLOYEE(

Ssn char(9) NOT NULL,

Fname varchar(40) NOT NULL,

Minit varchar(1) NOT NULL,

Lname varchar(40) NOT NULL,

Bdate date ,

Address varchar(40) NOT NULL,

City varchar(20) NOT NULL,

Stat varchar(2) NOT NULL,

Sex varchar(1) NOT NULL,

Super_SSN char(9),

DNO char(4) NOT NULL,

CONSTRAINT PK_Person PRIMARY KEY (Ssn),

CONSTRAINT FK_Emp FOREIGN KEY (DNO) REFERENCES Persons(DNO)

);

Insert values – Emp table

INSERT INTO EMPLOYEE (Ssn, Fname, Minit, Lname, Bdate, Address, City, Stat, Sex,

Salary, Super_ssn, Dno)

VALUES ('123456789','John', 'B', 'Smith','09-Jan-1965', '731 Fondren', 'Houston', 'TX', 'M', '30000',

'333445555', '5');

Print employee's first name, last name, address, and department number

SELECT Fname, Lname, Address, City, Stat, Dno

FROM EMPLOYEE;

Please see the marking scheme in next page.

Question Comments Marks

1. Identify business rules

(5 marks) /5

You need to list any business

rules identified.

2. Entity Relationship Diagram

(40 marks)

Identifying Entities /5

Identifying Attributes for /5

each entity

Identification of relationships /5

For use of correct symbols /5

and for the clarity

Mapping cardinalities correctly /1

0

Identifying and marking Primary /10

and foreign Keys 3. Relational Schemas /1