Data modeling assignment

[DATE]

Data Modeling Assignment DATABASE ASSIGNMENT HELP

DE PROGRAMMING ASSIGNMENT HELP Programmingassignmentshelp.net

SECTION B (SQL) : Answers 1.

Display full details of all customers.

select from i_customer;

2.

Display the registration, cost and status for all cars. Order by cost in descending order.

SELECT REGISTRATION, FROM

COST,

STATUS I_CAR

ORDER BY COST DESC;

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

3.

Display the car registration, current mileage and purchase date for all cars. Order by current mileage in ascending order.

SELECT REGISTRATION, MILES_TO_DATE AS “CURRENT_MILEAGE”, DATE_BOUGHT AS “PURCHASE DATE” FROM

I_CAR

ORDER BY MILES_TO_DATE ASC;

4.

Display all the cars registration, miles to date and status for all the cars that have not had a service but are available for hire. Order the list in descending order of miles to date.

SELECT REGISTRATION,

MILES_TO_DATE

AS

“CURRENT_MILEAGE”,

FROM WHERE AND

STATUS I_CAR

MILES_LAST_SERVICE STATUS

IS =

NULL ‘A’

ORDER BY MILES_TO_DATE DESC;

Read about Routing Protocol Assignment Help 5.

Display full details for all the bookings where amount due has not been paid.

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

SELECT * FROM

I_BOOKING

WHERE PAID=’N’

6.

Display the booking number, date rent started, period of rental and the expected end date of each rental. Label the expected end date of each rental as: ‘Expected Return Date’. Sort the output by the expected end date in ascending order.

SELECT BOOKING_NO,

DATE_RENT_START,RENTAL_PERIOD,

“EXPECTED FROM

(DATE_RENT_START+RENTAL_PERIOD)

RETURN

as

DATE” I_BOOKING

ORDER BY “EXPECTED RETURN DATE” ASC;

7.

Display full details for any car that: (a) costs more than $100,000.00 or the name of the car model starts with the letter ‘L’ and (b) belongs to group ‘A4‘ and has a registration whose second digit is 9.

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

SELECT * FROM

I_CAR

WHERE (COST

>’100000′

OR

MODEL_NAME

LIKE

‘L%’)

AND (CAR_GROUP_NAME=’A4′ AND SUBSTR(REGISTRATION,2,1)=’9′)

8.

Display full details for all bookings where the car has miles out value but the paid field has no value recorded. Both conditions need to be explicitly queried.

SELECT * FROM

I_BOOKING

WHERE

MILES_OUT

IS

NOT

NULL

AND PAID IS NULL;

Read more about Computer Network Assignment Help 9.

Display full details for all car models where the car group name is ‘A3’ or ‘B3’ AND the number of miles between services is either 12000 or 15000 miles. Find a way of optimising your code so that the complete SQL statement has only ONE logical operator (AND, OR, NOT).

SELECT * FROM

I_CAR

WHERE CAR_GROUP_NAME (SELECT FROM

www.programmingassignmentshelp.net

IN CAR_GROUP_NAME I_MODEL

help@programmingassignmentshelp.net

WHERE

CAR_GROUP_NAME

IN

(‘A3′,’B3’)

AND

MAINT_INT

IN

(‘12000′,’15000’));

10. Display the registration and miles traveled since last services and status for all the cars which have traveled more than 1500 miles since last service. Label the miles traveled since last service column “Miles Traveled” and order the query in descending order of miles traveled since lasted service.

SELECT REGISTRATION,

(MILES_TO_DATE-MILES_LAST_SERVICE)

AS

“MILES_TRAVELED”,

FROM WHERE

STATUS I_CAR

(MILES_TO_DATE-MILES_LAST_SERVICE)

>

1500

ORDER BY “MILES_TRAVELED” DESC;

Also Read: Database Design Techniques

SECTION C (Data Modelling): Answers Question 1

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

An ambulance driver may be assigned to many teams over time. A team comprises of a minimum of one driver but could have up to twelve. For driver, we store the name, address and birth date. For team, we store the team id and the location. We also need to store the date a driver is assigned to a team, the date a driver leaves the team and the rating of the driver within the team.

Driver_Master

Column Name

Type & Size

Constraint

Driver_name

Varchar2(50)

Driver Name

Driver_address

Varchar2(100)

Driver Address

Driver_Birthdate

Date

Driver Birthdate

Driver_ID

Number(10)

Driving_License_No

Varchar2(20)

PK

Description

Unique identifier for Driver

Driver License number

Team_Transaction

Column Name

Type & Size

Constraint

Description

Team_ID

Number(10)

FK

Team ID from Team_Master table

Driver_ID

Number(10)

FK

Driver ID from Driver_Master table

Transaction_ID

Number(10)

PK

Unique identifier for Transaction

Transaction_Date

Date

Transaction Date

Driver_Rating

Number(2)

Driver Rating

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

Team_Master

Column Name

Type & Size

Constraint

Description

Team_ID

Number(10)

PK

Unique identifier for Team

Location

Varchar2(20)

Location of Team

Team_History

Column_Name

Type & Size

Constraint

Description

Team_ID

Number(10)

FK

Team ID from Team_Master table

Driver_ID

Number(10)

FK

Driver ID from Driver_Master table

Transaction_Id

Number(10)

PK

Unique identifier for Transaction

Team_Join_date

Date

Date when Driver joined Team

Team_Leaving_date

Date

Date when Driver left team

Driver_Rating

Number(2)

Rating of the Driver

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

List of Entities:    

Driver_Master Team_Master Team_Transaction Team_Transaction_History

Question 2 A questionnaire is a research instrument consisting of a series of questions. We need to develop a model to store questionnaires and its underlying questions. A questionnaire records questionnaire name, primary contact name, start date and expected end date. A questionnaire may have many questions.

Each question has a question number, question text and a category description for the question. Read

about ER Diagram and Normalization Factor Assignment Questionnaire_Master

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

Column Name

Type & Size

Constraint

Description

Questionnaire_name

Varchar2(100)

Name of Questionnaire

Primary_contact_name

Varchar2(100)

Primary Contact Name

start_date

Date

Start Date of Questionnaire

expected_end_date

Date

Expected end date of Questionnaire

Questionnaire_Id

Number(10)

PK

Unique identifier for Questionnaire

Question_no

Number(10)

FK

Question no from Question_Master

Question_Master

Column Name

Type & Size

Constraint

Description

Question_no

Number(10)

PK

Unique identifier for Question

Question_text

Varchar2(100)

Question Text

Question_Description

Varchar2(200)

Question Description

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

List of Entities:  

Questionnaire_Master Question_Master

Question 3 An academic must be assigned to only one area of specialty. An area of specialty could have one or many academics associated with it. For academic, we store the first name, last name and date hired. For specialty, we store an id and a description.

Academic_Master

Column Name

Type & Size

First_Name

Varchar2(100)

First Name of Academics

Last_name

Varchar2(100)

Last Name of Academics

Date_Hired

Date

Date when academics hired

Academics_Id

Number(10)

PK

Unique identifier for Academics

Speciality_Id

Number(10)

FK, UNIQUE

Only Unique Values allowed

www.programmingassignmentshelp.net

Constraint

Description

help@programmingassignmentshelp.net

Question_Master

Column Name

Type & Size

Constraint

Description

Speciality_Id

Number(10)

PK

Unique identifier for Speciality

Description

Varchar2(200)

Speciality Description

Assignment_Transaction

Column Name

Type & Size

Constraint

Description

Academics_ID

Number(10)

FK

Academics identifier

Speciality_ID

Number(10)

FK

Speciality identifier

Transaction_ID

Number(10)

PK

Unique identifier for Transaction

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

List of Entities:

  

Academics_Master Speciality_Master Assignment_Transaction

Question 4 A researcher may be working on a number of research projects over time. A research project can have multiple researchers working on it but will only have a single head researcher but we do not need to know who the head researcher is. For researcher, we store the researcher identification number, his/her office number and area of specialty. We also need to store the date a researcher has started working on a research project; and research project title, project manager’s name and project administrator’s name and contact. Read about Computing and System Development Assignment Researcher_Master

Column Name

Type & Size

Constraint

Description

Researcher_id

Number(10)

PK

Unique identifier of Researcher

Office_number

Number(20)

Researcher office number

Area_of_speciality

Varchar2(50)

Researcher speciality

Researcher_Head

Char(1)

Accepts only Y or N, Y denotes that researcher is Head, N is for normal Researcher

Project_Master

Column Name

Type & Size

Start_date_of_project

Date

Project Start Date

Project_title

Varchar2(20)

Title of Project

www.programmingassignmentshelp.net

Constraint

Description

help@programmingassignmentshelp.net

Project_manager_name

Varchar2(50)

Name of Project Manager

Project_admin_name

Varchar2(50)

Name of Project Admin

Project_admin_contact

Number(20)

Contact of Project Admin

Project_id

Number(10)

PK

Unique Identifier of Project

Researcher_id

Number(10)

FK

Researcher ID from Researcher Master

Project_Allotment

Column Name

Type & Size

Constraint

Description

Project_ID

Number(10)

FK

Project Id from Project Master

Researcher_ID

Number(10)

FK

Researcher ID from Researcher Master

Researcher_Head_ID

Number(10)

Unique, Null Allowed

Researcher ID from Researcher Master only if Researcher Head is ‘Y’ in Researcher Master

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

List of Entities:   

Researcher_Master Project_Master Project_Allotment

Read more about Computer Programming Assignment Help

Programming Assignment Help UK is committed to plagiarism free assignments. This free Data Modeling Assignment Help is complete solution based on case study.

Programming Assignments Help is the best assignment help provider in the United Kingdom. Our online assignment writing help UK is especially dedicated for the students studying in all UK colleges and universities. Submit assignment to get the best quality assignment help.

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net