Database System Concepts
7th Edition
ISBN: 9780078022159
Author: Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher: McGraw-Hill Education
expand_more
expand_more
format_list_bulleted
Related questions
Concept explainers
Question
Transcribed Image Text:#below is the SQL Script: You can copy and paste to run
create database pharmacy;
use pharmacy;
create table branch (id int (3) not null auto_increment primary key, name varchar (50)
not null);
create table transaction_type (id int (1) not null auto_increment primary key, type
varchar (15) not null);
create table network (id int (2) not null auto_increment primary key, name varchar (30)
not null) ;
create table worker (id int (3) zerofill not null auto_increment primary key, fname
varchar (30) not null, Iname varchar (30) not null, contact varchar (13) not null);
create table worker_branch (worker_id int (3) not null references worker (id),
branch_id int (3) not null references branch (id), primary key (worker_id,branch_id));
create table transaction (id int not null auto increment primary key, worker id
int (3) not null references worker (id), branch_id int (3) not null, customer_no
varchar (13) not null, customer_id int (25));
You have been tasked to use the SQL script provided above by the database designer to explain how the
system works by way of providing the following:
1. The list of entities identified by the database designer.
2. Provide an ER diagram indicating all the relationships using UML.
3. Write query to insert the following records:
a. Deposit and Withdrawal into the transaction_type table as types.
b. Kumasi, Accra, Tamale, Bono Ahafo and Tema as branch names in the branch table.
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
Step by stepSolved in 3 steps with 1 images
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Similar questions
- Using MySQL Workbench, create a new schema for this assignment and import assignment2.sql. Reverse-engineer the database to produce the ER diagram for this database. Rely on the ERD to perform remaining tasks. Write and execute SQL commands to get information from the database: Display a single number showing the total number of customers in the database. Display a single number showing how many products categories are available (do not include duplicates). Display a single number showing how many shipping addresses are from Ontario. Join the payment_info and payments table together based on PK/FK. Display the count of records for each payment type. Display a single number showing how many invoices were issued in 2013. Join the customers and shipping_details tables together based on PK/FK. Display the province code and the number of records per province. Data should be sorted in descending order of the number of records. Display the lowest, highest, and mean amounts on the invoice…arrow_forwardGiven the following database schema, write relational algebra (not SQL) expressions for the questions below: Manufacturer (Manufacturer_id, name, address) Produced(Manufacturer_id, Part_id, Quantity) Part(Part_id, name, price) 1. List names of manufacturers and the names of the parts they produced. 2. List the name of each manufacturer who produced all parts that are recorded in the Part relation. 3. List the names of each part when a number of 50 or more of that parts are produced. Note that this quantity can be produced by one manufacturer of more. 4. List the names of manufacturers who live in "Hebron".arrow_forwardConsider the following relational schema and answer the following questions. Employee (Employee_Id, First_Name, Last_Name, Email, Phone_Number, Hire_Date, Job_Id, Salary, Commission_Pct, Manager_Id, Department_Id) a. Write a query to find the manager ID and the salary of the lowest-paid employee for that manager. b. Write a query to get the department ID and the total salary payable in each department. c. Write a query to get the average salary for each job ID excluding programmer. d. Write a query to get the total salary, maximum, minimum, average salary of employees (job ID wise), for department ID 90 only.arrow_forward
- Consider the Movie Stars database that contains the following relations: StarsIn(movieTitle, movieYear, starName) MovieStar(Name, address, gender, birthdate) The underlined columns are the primary keys and starName in the StarsIn table is a foreign key that references the primary key Name in the MovieStar table. For the following SQL query: SELECT movieTitle FROM StarsIn, MovieStar WHERE starName = Name and address LIKE ‘%Los Angeles%‘; Suppose there are no indexes in the database and both relations are stored in heap files. Write an efficient relational algebra expression (logical plan) that is the equivalent to this query and give a physical plan for this expression (choose the algorithm to implement each operation). Justify your answer. Hint: this is an open-ended question and you may justify your answers using any valid assump- tions. Please give detailed solution.arrow_forwardHow about a summary of the benefits associated with using tables in relational databases? Perhaps you could elaborate on the concept of NULL in a short essay.arrow_forwardNeed a query in SQL with these guidelines.arrow_forward
- (a) Write the sql syntax to create a table name Client with the following restrictions; Client(cid, cname, address, age, occupation, salary, payment_mode) Where cid is the primary key. cname do not accept null values. Payment_mode accepts only cash or credit entries. The default address is “vacoas". client is at least 25 years old. Salary is above Rs 30000. (b) Consider the schema of Customer table in part(a) and write SQL statements for the following queries. i. Using SQL, write the command to add a new column in the client table with the following data definition; (Column “gender" of char data type and field size of 1). ii. Using SQL, write the command to insert the values in the Client table. cid address age occupation salary Payment_mode gender cname 123 sam vacoas 31 manager 60000 cash m 124 david flacq 38 administrator 45000 credit m iii. Write the SQL query to display the cid of all employees. iv. Write the SQL code to list all administrators whose salary is greater than Rs…arrow_forwardPractice 1 (continued) 7. Show the structure of the EMP table. Create a query to display the name, job. hire date, and employee number for each employee, with employee number appearing first. Save your SQL statement to a file named plq7.sql. Name Null? Туре EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) СOMM NUMBER(7,2) DEPTNΟ NUMBER(2) 8. Run your query in the file plq7.sql. EMPNO ENAME JOB HIREDATE 7369 SMITH CLERK 17/12/1980 7499 ALLEN SALESMAN 20/02/1981 7521 WARD SALESMAN 22/02/1981 7566 JONES ΜΑΝAGER 02/04/1981 7654 MARTIN SALESMAN 28/09/1981 7698 BLAKE MANAGER 01/05/1981 7782 CLARK MANAGER 09/06/1981 7788 SCOTT ANALYST 09/12/1982 7839 KING PRESIDENT 17/11/1981 7844 TURNER SALESMAN 08/09/1981 7876 ADAMS CLERK 12/01/1983 7900 JAMES CLERK 03/12/1981 7902 FORD ANALYST 03/12/1981 7934 MILLER CLERK 23/01/1982 14 rows selected.arrow_forwardAll parts of this question use the following database schema: Flights(flno, from, to, distance) Aircraft(aid, aname, cruisingrange) Certified(eid, aid) Employees(eid, ename, salary) c) For each cruisingrange held by at least 3 aircraft, give the lowest salary of employees certified to fly one of those aircraft. Express in SQL only:arrow_forward
- 4. This question continues from Q3. (a) Write the insert SQL to populate the Zoom, BB, and the QR tables with the values given below. For each table, use only one insert to complete the insert task. The Duration column is initially empty. (b) Can you insert the ZOOM table first? Provide a reason according to the relational database theory if it is not possible. (c) Write a Delete SQL to remove student ‘M M’ from the BB table. If it is not possible to remove the row, provide a reason according to the relational database theory. You must not use SID in the SQL because only the name is given. (d) Write an Update DML that will compute and update the duration in minutesin the Zoom table. Show the updated table and the DML.arrow_forwardConsider the following relational schema:Course (cid, cname, dept, credit)Student (sid, sname, dept, gpa)Enroll (cid, sid, grade, semester) The meaning of these relations is straightforward and the keys are underlined. Write thefollowing queries in SQL. a) List all the students who never got a grade above ‘C’.b) Find the number of students not enrolled in any class.c) Find name of students in EE department who took all the courses offered by CSdepartment.arrow_forwardBased on the following ER model and business rules, write an SQL statement to create COURSE and COURSE_PREREQ table. Be sure to implement all related data integrity constraint. For each column, use the data type as you think appropriate. Business rule • A course may have more than one prerequisites. The prerequisite courses may have their own prerequisite courses (more than one). COUSE entity • CourseNo is a UID (unique identifier). • Credit hours should be greater than 0 and less than 7. COURSE_PREREQ entity • UID is the composite of CourseNo and CourseNo (i.e., prerequisite courseNo). COURSE COURSE_PREREQ CourseNo: UID Title Description CourseNo: LCourseNo: UID CreditHoursarrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
- Database System ConceptsComputer ScienceISBN:9780078022159Author:Abraham Silberschatz Professor, Henry F. Korth, S. SudarshanPublisher:McGraw-Hill Education
Starting Out with Python (4th Edition)Computer ScienceISBN:9780134444321Author:Tony GaddisPublisher:PEARSON
Digital Fundamentals (11th Edition)Computer ScienceISBN:9780132737968Author:Thomas L. FloydPublisher:PEARSON 
C How to Program (8th Edition)Computer ScienceISBN:9780133976892Author:Paul J. Deitel, Harvey DeitelPublisher:PEARSON
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781337627900Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Programmable Logic ControllersComputer ScienceISBN:9780073373843Author:Frank D. PetruzellaPublisher:McGraw-Hill Education
Database System Concepts
Computer Science
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:9780134444321
Author:Tony Gaddis
Publisher:PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:9780132737968
Author:Thomas L. Floyd
Publisher:PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:9780133976892
Author:Paul J. Deitel, Harvey Deitel
Publisher:PEARSON
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:9780073373843
Author:Frank D. Petruzella
Publisher:McGraw-Hill Education