#below is the SQL Script: You can copy and paste to runcreate 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, typevarchar (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, fnamevarchar (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 idint (3) not null references worker (id), branch_id int (3) not null, customer_novarchar (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 thesystem 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.

List of entities indentified are: Branch, Transaction Type, Network, Worker, Worker Branch,…

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.

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.

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Q: Using the SQL programming language, how would one go about changing the structure of an existing…

A: ALTER table command The structure of a table can be changed using the ALTER TABLE command. It can…

Q: 1. Write a database description for each of the relations shown, using SQL DDL (shorten, abbreviate,…

A: Given

Q: Create tables based on the below given relational model. Define your own data type to the…

A: According to the question below the Complete Query:

Q: Please write SQL codes to create below dvd database table based on the previous slide (slide 15)…

A: In this problem, we need to create the DVD table using SQL. The required columns for the DVD table…

Q: You are interested in compiling a small SQL database of the contents of this course. You decide you…

A: In this question, we have to create a spreadsheet on Excel which has the description of both the…

Q: Instructions Using the data provided in the Ch13_SaleCo; database, solve the following problems.…

A: The data is present in three different tables- DWTIME DWDAYSALESFACT DWPRODUCT, So, we must join…

Q: Instructions Using the data provided in the Ch13_SaleCo; database, solve the following problems.…

A: We will join DWDAYSALESFACT and DWCUSTOMER tables to populate Total sales and group the result by…

Q: What is the SQL command to list the number of product sales (number of rows) and total sales by…

A: SELECT T.TM_MONTH, P.P_CATEGORY, P.P_CODE, COUNT(*) AS NUMPROD, SUM(S.SALE_UNITS*S.SALE_PRICE) AS…

Q: 1) Create table student(student_id, student_name,address,year_of_joining,specialization). Note: the…

A: The Answer is in step2 As Per Bartleby guildlines we solved only one question

Q: This is a simple list table of a company trying to keep track of parts that they sell and orders…

A: The dependency diagram for the given table is as follows: The table needs to be broken down into…

Q: I'm wondering how to start this assignment?

A: Since it is a SQL assignement, you have to setup your environment for this . Step 1: Download Xampp…

Q: 2. Next, the script creates the smallest number of indexes such that processing of each one of the…

A: SEE SOLUTION IN STEP 2

Q: insert the tasks in a batch into the database. In other words, they want to insert the rows all…

A: According to given answer , value will inserted one by one. So below i write code to insert two rows…

Q: Write an SQL statement to add three new rows to the PET_OWNER table.Assume that OwnerID is a…

A: Given: Write an SQL statement to add three new rows to the PET_OWNER table.Assume that OwnerID is a…

Q: IN detail explain each digram (a) Description of each table (b) Display of each table itself…

A: The objective of the question is to understand the structure of the given tables and demonstrate the…

Q: To make the TAL Distributors database run more efficiently, the DBA denormalized part of the data,…

A: A DBA, short for Database Administrator, is a professional responsible for managing and maintaining…

Q: 9. List the ID, category, inventory value of the product whose inventory value is greater than…

A: Note:- You can't use alias name(temporary name) in where clause. like, select prodID, category,…

Q: Consider the insurance database of Figure 3.17, where the primary keys areunderlined. Construct the…

A: In a relational database, a primary key, also identified as main keywords, is a key that is unique…

Concept explainers

SQL Query

Structured Query Language is the full form of SQL. It is one of the standard languages to deal with the relational database. It is used to insert, update, delete, and search the records stored in a RDBMS. The relational databases are created and managed by using SQL.

Question

#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.

SEE SOLUTION Check out a sample Q&A here

1. Entities in the database:

VIEW

2. E-R Diagram-

VIEW

3. Queries-

VIEW

Step by step

Solved in 3 steps with 1 images

SEE SOLUTION Check out a sample Q&A here

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…
Given 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".
Consider 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.
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 eﬀicient 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.
How 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.
Need a query in SQL with these guidelines.
(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…
Practice 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.
All 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:
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.
Consider 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.
Based 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 CreditHours