Question
In: Computer Science
Consider the following relational schema (the primary keys are underlined and foreign keys are italic) ITEM(ItemName,...
Consider the following relational schema (the primary keys are
underlined and foreign keys are italic)
ITEM(ItemName, ItemType, ItemColour)
DEPARTMENT(Deptname, DeptFloor, DeptPhone, Manager)
EMPLOYEE(EmpNo, EmpFname, EmpSalary, DeptName, SupervisedBy)
SUPPLIER(SupNo, SupName)
SALE(SaleNo, SaleQty, ItemName, DeptName)
DELIVERY(DeliNo, DeliQty, ItemName, DeptName, SupNo)
Write the SQL statements for the following queries:
C1. Find the names of items sold on first and second
floors.
[1 mark]
C2. For each department, list the department name and average
salary of the employees where the average salary of the employees
is great than $28,000.
[1 mark]
C3. List the name and salary of the managers with no more than
10 employees.
[2 marks]
C4. List the names of the employees who earn more than any
employee in the Deliver department. [2 marks]
C5. For each department that sells items of type E, list the
department name and the number of the employees. [2 marks]
C6. Find the supplier name who supplies the least items.
Solutions
venereology answered 6 months agoRelated Solutions
Consider the following relational schema about a University (the primary keys are underlined and foreign keys...
Consider the following relational schema about a University (the
primary keys are underlined and foreign keys are italic)
STUDENT(StudentID, name, major, year, age) CLASS(ClassName,
meetsAt, room, LecturerID) ENROLLED(StudentID, ClassName, mark)
LECTURER(LecturerID, name, DepartmentID) DEPARTMENT(DepartmentID,
name) Write the SQL statements for the following query:
B1. Find the age of the oldest student.
B2. Find the ID’s of lecturers whose name begins with “K” \
B3. Find the age of the youngest student who is enrolled in
Mechatronics.
B4. Find the age...
1. Write CREAT TABLE statements for the following tables (primary keys are underlined, foreign keys are...
1. Write CREAT TABLE statements for the following tables
(primary keys are underlined, foreign keys are in italic and bold).
Make sure you have all needed constraints and appropriate datatypes
for attributes:
Student (stID, stName, dateOfBirth,
advID, majorName, GPA)
Advisor (advID, advName, specialty)
2. Insert several records in each table.
1. Write CREAT TABLE statements for the following tables (primary keys are underlined, foreign keys are...
1. Write CREAT TABLE statements for the following tables
(primary keys are underlined, foreign keys are in italic and bold).
Make sure you have all needed constraints and appropriate datatypes
for attributes: Student (stID, stName, dateOfBirth, advID,
majorName, GPA) Advisor (advID, advName, specialty) 2. Insert
several records in each table.
Create a Database Schema for a hotel reservation system. indicate the Primary Keys, Foreign Keys, and...
Create a Database Schema for a hotel reservation
system. indicate the Primary Keys, Foreign Keys, and the
one-to-one or one-to-many relationships between the tables. Also
describe in a small paragraph the scope of the back-end database,
by explaining the different tables and their relations in
your schema.
1. Consider the following relations. The primary keys are underlined. All attributes are of type string...
1. Consider the following relations. The primary keys are
underlined. All attributes are of type string if not indicated
otherwise.
Student(s_ID, s_name, s_degree: integer, advisorID, d_ID)
Lecture(l_ID, l_name,l_degree: integer, p_ID, d_ID)
Register(s_ID,l_ID,score: integer, Semester)
Professor(p_ID,p_name, d_ID)
Department(d_ID, d_name, address)
a. Find the names of professors who have taught in every
semester.
b. List the names of lectures that the CISE department offers
but that are taught by a professor whose department is not
CISE.
c....
Q3. Convert the conceptual database into a relational database schema. Identify the primary key and foreign...
Q3. Convert the conceptual database into a relational
database schema. Identify the primary key and foreign key
constraints.
Q4. Show all the functional dependencies that should
hold among the attributes.
Q5. Design relation schemas for the database that are
each in 3NF. Specify the primary and foreign key attributes of each
relation.
Hi expert!
Can you help me with the above questions? I'm not asking
you to solve my questions but want to find what each questions
actually requires me...
Create the following tables. The underlined bold column names are the primary keys. Make sure to...
Create the following tables. The underlined
bold column names are the primary keys. Make sure
to specify the primary and foreign key constraints in your create
table statements.
customer: (cus_code:integer,
cus_lname:varchar(20), cus_fname:varchar(20), cus_initial:char,
cus_areacode:integer,cus_phone:integer).
invoice: (inv_number:integer,
cus_code:integer, inv_date:date,
foreign key cus_code references customer(cus_code))
vendor:(vend_code:integer,vend_name:varchar(30),vend_contact:varchar(30),vend_areacode:integer,vend_phone:integer)
product:(prod_code:integer,
prod_desc:varchar(50), prod_price:integer,
prod_quant:integer,vend_code:integer,
foreign key (vend_code) referenecs
Vendor(vend_code))
line: (inv_number:integer,
prod_code:integer
,line_units:integer,
foreign key (inv_number) references
Invoice(inv_number),
foreign key (prod_code) references
Product (prod_code) )
Consider the following three tables, primary and foreign keys. Table Name SalesPeople Attribute Name  
Consider the following three tables, primary and foreign
keys.
Table Name
SalesPeople
Attribute
Name
Type
Key Type
EmployeeNumber
Number
Primary Key
Name
Character
JobTitle
Character
Address
Character
PhoneNumber
Character
YearsInPosition
Number
Table Name
ProductDescription
Attribute
Name
Type
Key Type
ProductNumber
Number
Primary Key
ProductName
Character
ProductPrice
Number
Table Name
SalesOrder
Attribute
Name
Type
Key Type
SalesOrderNumber
Number
Primary Key
ProductNumber
Number
Foreign Key
EmployeeNumber
Number
Foreign Key
SalesOrderDate
Date
Assume that you...
Consider the following three tables, primary and foreign keys. Table Name SalesPeople Attribute Name  
Consider the following three tables, primary and foreign
keys.
Table Name
SalesPeople
Attribute
Name
Type
Key Type
EmployeeNumber
Number
Primary Key
Name
Character
JobTitle
Character
Address
Character
PhoneNumber
Character
YearsInPosition
Number
Table Name
ProductDescription
Attribute
Name
Type
Key Type
ProductNumber
Number
Primary Key
ProductName
Character
ProductPrice
Number
Table Name
SalesOrder
Attribute
Name
Type
Key Type
SalesOrderNumber
Number
Primary Key
ProductNumber
Number
Foreign Key
EmployeeNumber
Number
Foreign Key
SalesOrderDate
Date
Assume that you...
• Relational Schema Create a relational database schema consisting of the four relation schemas representing various...
• Relational Schema
Create a relational database schema consisting of the four
relation schemas representing various entities recorded by a
furniture company.
Write CREATE TABLE statements for the following four relation
schemas. Define all necessary attributes, domains, and primary and
foreign keys.
Customer(CustomerID, Name, Address)
FullOrder(OrderID, OrderDate, CustomerID)
Request(OrderID, ProductID, Quantity)
Product(ProductID, Description, Finish, Price)
You should assume the following: Each CustomerID is a number
with at most three digits, each OrderID is a number with at most
five digits,...
ADVERTISEMENT
ADVERTISEMENT
Latest Questions
- A 0.0038 kg leaf falls 16.8 m to the ground, where it lands with a speed...
- Problem 1: In the figure to the right, m1=20.0kg and α=53.1o . The coefficient of kinetic...
- A palindrome is a string whose reversal is identical to the string. For example, 110010011 is...
- Explain how the shape of the load duration curve for a particular system affects the selection...
- For physics discussion post. Please TYPE the response as I struggle with reading written information. Explain...
- Consider a file system using a multi-level indexing scheme in inodes as the file organization. In...
- 1a) A sample of a new cleaning product, "Joe's Famous Bleach Cleaner," with a mass of...
ADVERTISEMENT