Skip to document
Welcome to StudocuSign in to access the best study resources
Guest user
0followers
HomeMy LibraryAsk AI
  • You don't have any recent items yet.
My Library
  • You don't have any courses yet.
  • You don't have any books yet.
  • You don't have any Studylists yet.

Practical 2

this is a practice material that focuses on increrasing your knowledge...
Course

Computer Science (ComSci1)

76 Documents
Students shared 76 documents in this course
Academic year: 2023/2024
Uploaded by:
Anonymous Student
This document has been uploaded by a student, just like you, who decided to remain anonymous.
Rhodes University

Recommended for you

Comments

Please sign in or register to post comments.

Students also viewed

Related documents

Preview text

COMPUTER SCIENCE 112 2023

Module Name Databases

Practical Number 2

Practical Name Databases practical 2 ( 103 Marks)

Week Starting 28 August 2023

Due Date 1 September 2023 (Upload on RUConnected)

Submission Submit the practical before 23:59 as stipulated on RUConnected

Practical Information:

Hand in:

All work is to be submitted on RUConnected by Friday 1 September at 23:59. If you fail to

submit your work by the deadline, you will automatically be assigned a mark of zero (0).

You will not be able to submit your work at a later date on RUConnected, and you are not

allowed to email your work to your tutor after the deadline. If you are worried that you won’t

be able to submit your work in time, submit what little you have so that you at least get a

mark. You are also encouraged to save your work repeatedly in draft form on RUConnected

before submitting the final version. For every submission you make on RUConnected, you

should always download the file and check if it is correct.

Plagiarism:

By submitting this practical, you agree that the work submitted is your own and agree to abide

by the Rhodes University Plagiarism Policy (available at Here).Please see your Computer

Science departmental handbook for a friendlier version of this document. In short, don’t

cheat. Cheating is lame. Only submit work that is your own, even if you worked as a group.

Don’t give your work to another student to copy. Don’t copy answers word for word from the

slides, books, notes or internet sources either. Always put things in your own words.

Practical 2 : 103 Marks
Please ensure that you upload database and all the relevant files on RUConnected before the
deadline. Please look carefully at the mark allocations for each question.

Question 1 [ 10 marks]

1 Using the products and orders table, create the following relationship, one product can

be associated with multiple orders, but each order can only be associated with one

product. (One to many relationship). [6]

1 Make sure to enforce referential integrity in the relationship. [ 4 ]

Question 2 [ 7 marks]

From the products and orders table create a simple query using query wizard to select data

from two tables. The query should only include the following fields:

  • Order_ID
  • Product_ID
  • Product_Name
  • Price
  • First_name
  • Last_name

This query should return the data as shown below (Figure 1). Name the query “Orders

Query”. [ 7 ]

Figure 2

Question 4 [ 14 marks]

From the products and orders table create a query using query design.

4 The query should include the following fields:

  • Order_ID
  • Product_ID
  • Product_Name
  • Price
  • Quantity Ordered
  • Total Cost: A calculated field
  • Full Name: A calculated field

4 Sort the results by ‘Quantity Ordered’ in descending order. [14]

This query should return the data as shown below (Figure 3).

Name the query “Calculated Field Query”.

Figure 3

Question 5 [ 7 marks]

From the products_0 table, create an update query using design query.

  • The query should update 'Quantity in Stock' field.
  • The query should set all values less than 30 in the 'Quantity in Stock' field to 30.
  • Save the query as “Update Stock”.
  • Execute the query and check if the 'Quantity in Stock' values are updated.
  • Sort the table by ‘Quantity in Stock’ in ascending.

Question 6 [ 6 marks]

Create a query that counts the number of orders in each category. Name the query "Order

Category Details" This query should return the data as shown below (Figure 5). [ 6 ]

Figure 5

Question 7 [ 18 marks]

Given an “Employees” table with the following fields:

  • EmployeeID (Primary Key): A unique identifier for each employee.
  • FirstName: The first name of the employee.
  • LastName: The last name of the employee.
  • Department: The department in which the employee works (Sales, Marketing, Human

Resources, Engineering).

  • Position: The job position or title of the employee.
  • Salary: The salary of the employee.
  • HireDate: The date when the employee was hired.

Here is an example of an entry in the "Employees" Table:

EmployeeID FirstName LastName Department Position Salary HireDate

1 Rose Jacobs Sales Sales
Associate
50000 2020/03/

Write an SQL statement that:

7 Retrieves all information about all employees. [2]

7 Retrieves the first and last names of employees who work in the "Sales" department.[ 4 ]

7 Retrieves the first and last names of employees who were hired after January 1, 2022.[4]

7 Counts the number of employees in each department. [ 3 ]

7 Retrieves the first and last names of employees who have a salary greater than 60000

and work in the "Engineering" department. [5]

Question 8 [ 31 marks]

8 .1 Examine the table shown below.

BranchNo BranchAndress telNos

B001 8 Jefferson Way, Portland,

OR 97201

503 - 555 - 3618, 503- 555 - 2727,
503 - 555 - 6534

B002 City Center Plaza, Seattle,

WA 98122

206 - 555 - 6756, 206- 555 - 8836

B003 14 – 8th Avenue, New York,

NY 10012

212 - 371 - 3000

B004 20 – 18 th Avenue, Seattle,

WA 98128

206 - 555 - 3131, 206- 555 - 4112

(a) Why is this table not in 1NF? [2]

(b) Describe and illustrate the process of normalizing the data shown in this table to third

normal form (3NF). [4]

(c) Identify the primary and foreign keys in your 3NF relations. [3]

8 .2 Examine the table shown below.

Was this document helpful?

Practical 2

Course: Computer Science (ComSci1)

76 Documents
Students shared 76 documents in this course

University: Rhodes University

Was this document helpful?
COMPUTER SCIENCE 112 2023
Module Name
Databases
Practical Number
2
Practical Name
Databases practical 2 (103 Marks)
Week Starting
28 August 2023
Due Date
1 September 2023 (Upload on RUConnected)
Submission
Submit the practical before 23:59 as stipulated on RUConnected
Practical Information:
Hand in:
All work is to be submitted on RUConnected by Friday 1 September at 23:59. If you fail to
submit your work by the deadline, you will automatically be assigned a mark of zero (0).
You will not be able to submit your work at a later date on RUConnected, and you are not
allowed to email your work to your tutor after the deadline. If you are worried that you won’t
be able to submit your work in time, submit what little you have so that you at least get a
mark. You are also encouraged to save your work repeatedly in draft form on RUConnected
before submitting the final version. For every submission you make on RUConnected, you
should always download the file and check if it is correct.
Plagiarism:
By submitting this practical, you agree that the work submitted is your own and agree to abide
by the Rhodes University Plagiarism Policy (available at Here).Please see your Computer
Science departmental handbook for a friendlier version of this document. In short, don’t
cheat. Cheating is lame. Only submit work that is your own, even if you worked as a group.
Don’t give your work to another student to copy. Don’t copy answers word for word from the
slides, books, notes or internet sources either. Always put things in your own words.

Students also viewed

Related documents