4.1) For the following entity relationship diagram write MySQL code for creation of three tables. You can assume reasonable datatypes and len for each attribute WORK ALLOCATION has INTEGER PK,FK1 StaffID INTEGER PK,FK2 DepartmentID INTEGER VARCHAR(30) PK StaffID STAFF StaffName DateOfBirth DATETIME Salary DECIMAL(10,2) PercentageTime DOUBLE involves DEPARTMENT manages PK DeparmenttID INTEGER -OH FK1 DepartmentName VARCHAR(30) Budget ManagerID DOUBLE INTEGER 4.2) The book database has the following Relation Schema: Author (AuthorNum, AuthorLast, AuthorFirst) Publisher (PublisherCode, PublisherName, City) Book (BookCode, Title, Publisher Code, DateFirstPublished, Type, Price, Paperback) Branch (BranchNum, Branch Name, Branch Location, City, NumEmployees) Inventory (BookCode, Branch Num, OnHand) Wrote (BookCode, AuthorNum, Sequence) Using the above schema, write MySQL statements for the following queries (a & b): a. Find the books whose prices are more than the average books price. b. List publisher names and their locations for those publishers that have the letter r in the third position of their names. 11 =
4.1) For the following entity relationship diagram write MySQL code for creation of three tables. You can assume reasonable datatypes and len for each attribute WORK ALLOCATION has INTEGER PK,FK1 StaffID INTEGER PK,FK2 DepartmentID INTEGER VARCHAR(30) PK StaffID STAFF StaffName DateOfBirth DATETIME Salary DECIMAL(10,2) PercentageTime DOUBLE involves DEPARTMENT manages PK DeparmenttID INTEGER -OH FK1 DepartmentName VARCHAR(30) Budget ManagerID DOUBLE INTEGER 4.2) The book database has the following Relation Schema: Author (AuthorNum, AuthorLast, AuthorFirst) Publisher (PublisherCode, PublisherName, City) Book (BookCode, Title, Publisher Code, DateFirstPublished, Type, Price, Paperback) Branch (BranchNum, Branch Name, Branch Location, City, NumEmployees) Inventory (BookCode, Branch Num, OnHand) Wrote (BookCode, AuthorNum, Sequence) Using the above schema, write MySQL statements for the following queries (a & b): a. Find the books whose prices are more than the average books price. b. List publisher names and their locations for those publishers that have the letter r in the third position of their names. 11 =
Related questions
Question
Transcribed Image Text:4.1) For the following entity relationship diagram write MySQL code for creation of three tables. You can assume reasonable datatypes and len
for each attribute
WORK ALLOCATION
has
INTEGER
PK,FK1 StaffID
INTEGER
PK,FK2 DepartmentID INTEGER
VARCHAR(30)
PK StaffID
STAFF
StaffName
DateOfBirth DATETIME
Salary
DECIMAL(10,2)
PercentageTime DOUBLE
involves
DEPARTMENT
manages
PK DeparmenttID INTEGER
-OH
FK1
DepartmentName VARCHAR(30)
Budget
ManagerID
DOUBLE
INTEGER
4.2) The book database has the following Relation Schema:
Author (AuthorNum, AuthorLast, AuthorFirst)
Publisher (PublisherCode, PublisherName, City)
Book (BookCode, Title, Publisher Code, DateFirstPublished, Type, Price, Paperback)
Branch (BranchNum, Branch Name, Branch Location, City, NumEmployees)
Inventory (BookCode, Branch Num, OnHand)
Wrote (BookCode, AuthorNum, Sequence)
Using the above schema, write MySQL statements for the following queries (a & b):
a. Find the books whose prices are more than the average books price.
b. List publisher names and their locations for those publishers that have the letter r in the third position of their names.
11
=
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
Step by step
Solved in 2 steps
