#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

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 a scenario in which the SQL statement is of the form SELECT DISTINCT c1, c2, c3 from T. Why might it make sense to create an index to optimize this query?
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.
Create a SQL statement of a read-only view, name it as ViewE, that will contain Gender and the number of clients per gender in the CLIENTS table.
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.
Using the Schema - data/statistics Write down the following SQL queries. Assume appropriate data types for each attribute by yourself. 1) Create Bill table. 2)Assume you forgot to set SID as foreign key during creation of Bill table, by default SID is declared as null.Now you have to declare SID as foreign key. 3)Add constraint on names of customer that ensures that names must start with alphabet. 4)Delete constraint that was added in statement 3. 5)Add a new column in any one of the tables. 6)Delete the added column in statement 5 7)Show name of all those customers who have placed order after 23 June 2014 using subquery. 8)Show the data of those customers to who items were sold with SID 1 or 3 or 6 9)Show names of customers and items names of items sold to him 10)Make a vertical partition on customer table in such a way that changes in partitioned portion must be reflected on original table. 11) Update the city of customer as ‘Lahore’ where customer id is 3 12)Delete all those records…
1. Provide a general syntax or template of TRANSACTION in SQL.
Using the relational database given in lab work no. 2, write the following queries in SQL:1) Which course titles were given by at least two different instructors during the past?2) Which departments have at least two instructors and at least ten students?3) Which students took COMP117 from Cem Kalyoncu and COMP124 from Zafer Erenel?4) Which students are advised by Soydan Redif and took at least one course from Ezgi Ulker?5) Which students took multiple courses from Zafer Erenel but not a single course from Vesile Evrim?6) For each department, find out the highest salaried instructor name and the highest total credited student name.You should also present the output of each query. ////the database of Lab 2 is on attached file)
q/ Consider the employees database, where the primary keys are underlined. Give an expression in SQL for each of the following queries. Employee(ID, name, age, address, salary, departmet, position, ministry_name) Ministry(ministry name, address, No of employee, No of dept., category) 1- Find all employees who work as consultant in ministeries that have more than 7 departments and their name include the substring 'mo'. 2- 2- Find the ministries names that have more than 60 employees and at least 10 departments. 3- 3- delete the all employees that do not have address.
Consider the relational schema shown in Figure 8.8. Write each of the following queries on the schema, using SQL.i. Find the company with the most employees. ii. Find the company with the smallest payroll. iii. Find those companies whose employees earn a higher salary, on average, than the average salary at First Bank Corporation.
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.