[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