Solution file attached please check
CS405 Assignment 1 Solution and Discussion
Database Programming Using Oracle 11g – CS405
Semester: Fall 2019
Lectures Covered : 01-07
Total Marks: 20
Due Date: 14/11/2019
Students will be able to learn:
• Entity Relationship Diagram
• Structured Query Language
• How to write SQL Statement
Please read the following instructions carefully before submitting assignment:
It should be clear that your assignment will not get any credit if:
The assignment is submitted after due date.
The assignment is submitted via email.
The assignment is copied from Internet or from any other student.
The submitted assignment does not open or file is corrupt.
It is in some format other than .doc/docx.
Note: All types of plagiarism are strictly prohibited.
For any query about the assignment, contact at
Question no 1
You are required to draw Entity Relationship Diagram of the following scenario to show how you would track this information. Show entity names, primary keys, attributes for each entity, relationships between the entities and cardinality.
“A shipping company named ABC shippers keeps up-to-date information upon the processing and current location of each shipped item. For record keeping, following product information is stored: product name, product ID and quantity. Products are made up of many components and each component can be supplied by one or more suppliers. The following component information is stored in databases for record keeping: component ID, name, description, suppliers, and products in which they are used.”
Keep the following assumptions in mind while drawing ERD
I. A supplier can exist without providing components.
II. A component does not have to be associated with a supplier.
III. A component does not have to be associated with a product. Not all components are used in products.
IV. A product cannot exist without components.
Question no 2
Consider the following table and its attributes:
Employee (emp_id, emp_name, designation, phone_no, address, joining_date, salary, department)
You are required to use this table to write SQL statements for the following cases:
a) Display the name and annual salary of all employees.
b) Display the name of all employees whose name is at least 4 characters long.
c) Display the name and department of the employees who earns the maximum salary.
d) Display data for all employees who earn between 10000 and 20000.
e) Count no. of employees working in each department.
Create an ERD for a car dealership. The dealership sells both new and used cars, and it operates a service facility (see Figure B.2). Base your design on the following business rules:
A salesperson may sell many cars, but each car is sold by only one salesperson. A customer may buy many cars, but each car is bought by only one customer. A salesperson writes a single invoice for each car he or she sells. A customer gets an invoice for each car he or she buys. A customer may come in just to have his or her car serviced; that is, a customer need not buy a car to be classified as a customer. When a customer takes one or more cars in for repair or service, one service ticket is written for each car. The car dealership maintains a service history for each of the cars serviced. The service records are referenced by the car’s serial number. A car brought in for service can be worked on by many mechanics, and each mechanic may work on many cars. A car that is serviced may or may not need parts (e.g., adjusting a carburetor or cleaning a fuel injector nozzle does not require providing new parts).
Component(CompID, CompName, Description) PK=CompID
Product(ProdID, ProdName, QtyOnHand) PK=ProdID
Supplier(SuppID, SuppName) PK = SuppID
CompSupp(CompID, SuppID) PK = CompID, SuppID
Build(CompID, ProdID, QtyOfComp) PK= CompID, ProdID
CS301 Quiz 1 Solution and Discussion
CS301 – Data Structures5
UNSOLVED MTH404 Assignment 2 Solution and Discussion
MTH404 - Dynamics5
org.jetbrains.kotlin.util.KotlinFrontEndException: Exception while analyzing expression
SOLVED MTH304 Assignment 2 Solution and Discussion
MTH304 - Statics4
STAT408 Assignment 1 Solution and Discussion
STAT408 - Experimental Design Analysis4
SOLVED FIN725 GDB 1 Solution and Discussion
FIN725 - Credit & Risk Management3
SOLVED MGT713 GDB 1 Solution and Discussion
MGT713 - Production / Operations Management3
SOLVED MTH633 Assignment 1 Solution and Discussion
MTH633 - Group Theory3
SOLVED CS510 Assignment 2 Solution and Discussion
CS510 - Software Requirements and Specifications2
UNSOLVED BIF501 Assignment 1 Solution and Discussion
BIF501 - Bioinformatics II2
SOLVED BNK603 Assignment 1 Solution and Discussion
BNK603 - Consumer Banking2
SOLVED CS510 Assignment 1 Solution and Discussion
CS510 - Software Requirements and Specifications2
SOLVED MTH621 Assignment 1 Solution and Discussion
MTH621 - Real Analysis I2
UNSOLVED BNK601 Quiz 2 Solution and Discussion
BNK601 - Banking Laws & Practices2
MTH101 Assignment 1 Solution and Discussion
MTH101 - Calculus And Analytical Geometry1
SOLVED How to Netinstall RouterBoard/OS System
BIO734 Assignment 1 Solution and Discussion
BIO734 - Advances in Cell Biology1
BIF604 Assignment 1 Solution and Discussion
BIF604 - Special Topics in Bioinformatics1
UNSOLVED BIF601 Assignment 1 Solution and Discussion
BIF601 - Bioinformatics Computing I1
BIO731 Assignment 1 Solution and Discussion
BIO731 - Advanced Molecular Biology1