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
SQL Queries:
Given the following schema. Primary keys are underlined and highlighted in yellow and foreign keys are bold. The meaning of the relations is straightforward.
Medical_Staff(MS_Id, MS_First, MS_Last, MS_PhoneNumber, MS_Occupation, Salary)
Blood_Bank(Bank_Id, Bank_Name, Bank_Street, Bank_City, Bank_St, Bank_PhoneNumber)
Donor(Donor_Id, Donor_First, Donor_Last, Donor_BloodType, Donor_City, Donor_St, DOB, TotalNumberOfDonations)
Donation_Record(Donor_Id, Donation_Date, Donation_Amount)
Employee(MS_Id, Bank_Id)
Storage(Donor_Id, Donation_Date, Bank_Id)
Write SQL queries to answer the following questions:
a.Create a view to list of all employees for any Blood Bank that is in a city named St. Paul. (i.e., view output includes MS_Last, MS_First, and Bank_Id). Sort by Bank_Id, MS_Last, MS_First.
b.Find full names of all donors who donated in 2021. (Hint: use IN)
c.Create a stored procedure that takes two inputs: a Medical Staff identifier (MS_Id) and new salary. The procedure then updates the Medical_Staff table with the new salary.
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution
Trending nowThis is a popular solution!
Step by stepSolved in 2 steps
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
- Design your application's/organization's database. You need to create the 3 tables for each of the services offered. You must provide at least 5 examples in each of the table. There must be a table in which you have prices linked to the services you are providing. THIS IS THE NAME & DESCRIPTION OF THE ORGANIZATION FOR WHICH YOU NEED TO CREATE 3 TABLES FOR EACH SERVICE PROVIDED *Name of the Organization* :: ECOHUB *Description of the Organization* :: EcoHub is a sustainability-focused organisation that seeks to encourage environmentally friendly behaviours, increase public knowledge of environmental problems, and offer solutions for people, organisations, and communities to reduce their carbon footprint and help make the world a greener place. We think that many little individual actions can have a big influence on the environment when taken together. *Services Offered by the Organization are*1. EcoConsultancy : We provide individualised eco-consultancy services to people and…arrow_forwardTask 1:Create a database Entity and Relationship Diagram (ERD) for the following application:Please include answer with detailed explanationKhair Application: The Application idea: It is a mobile application called (Khair) to help people. This assistance is moral, material. This assistance is also provided by good people in our community. Example to understand the Application idea more: For example, a person urgently needs a medical chair or bed and does not have the ability to purchase what he needs. The medical chair or bed is requested through the application, and whoever has what he requests initiates the request through the application’s messages and determines the meeting place. Application models (page): Login included: login registration login included: user name password New registration included: name E-mail Password phone number date National Identification Number Home page included: Request - applicant name - address - send message Messages list form included: list of…arrow_forwardconvert it to relations that are normalizationarrow_forward
- The following three tables make up a simple reservation system for a small campground. The database should allow for a camper to make multiple reservations for future dates and for a camping spot to have several reservations for upcoming visits. Table: Camper CAMPER_NUMBER 1000 1001 1002 1003 CAMPER_LAST_NAME Jones Schmidt Williams Cooper CAMPER_FIRST_NAME Jamie Pat Clifford Amanda CAMPER_ADDRESS 1278 Essex Pl 4954 Spangled Way 956 Segull Lane P. O. Box 998877 CAMPER_CITY Birmingham El Paso Portland Portsmouth CAMPER_STATE AL TX ME OH CAMPER_ZIP_CODE 45251 79919 4108 45662 CAMPER_DRIVERS_LICENSE JJ998743-98 87632434 WIL885123 765A876B897 CAMPER_EMAIL jjones@somewhere.com patwonderfu34l@nowhere.net williams98342@foomail.com coopera@nowhere.net Table: Spot SPOT_NUMBER 101 102 103 104 SPOT_NAME The Pines The Glade Teardrop Spot Tent In Trees…arrow_forwardThe database should contain the following six tables: Artists (artist_id: char(30), artist_name: char(30), artist_pop: int(5))Tracks (track_id: char(30), track_name: char(30), duration: int(10), tempo:real) Record (artist_id: char(30), track_id: char(30))Users (user_id: char(30), user_name: char(30), age: int(5), nationality: char(30), num_track_listened: int(10))Listen (user_id: char(30), track_id: char(30))Follow (user_id: char(30), artist_id: char(30), follow_date: datetime) Write a single SQL query for each request below. Find the ids of those users who have listened to at least one track but have not followed any artists. Find the ids and names of those users who have listened to some tracks recorded by the artist named 'Adele'. Find the ids and names of those users who have not listened to any track recorded by the artist named 'Adele'. Use ''NOT EXISTS'' to answer this query.arrow_forwardWrite SQL statements to create at the two tables for the database. The tables must have at least three relevant columns with appropriate types, a primary key and at least one table should have a foreign key and the related foreign key constraints. CREATE TABLE students ( Student_id int NOT NULL PRIMARY KEY, Stud_firstname VARCHAR(30) NOT NULL, Stud_lastname VARCHAR(30) NOT NULL, Stud_email VARCHAR(80) NOT NULL UNIQUE advisor_id int, ); create table advisor ( advisor_id int not null primary key, stud_id int, adv_firstname varchar(30), adv_lastname varchar(30), foreign key(student_id) references students(student_id) ); [keep getting error, need help correcting]arrow_forward
- Topic: Database Software: SQLite Schema Table: CREATE TABLE name_basics (nconst VARCHAR(45) NOT NULL, primaryName VARCHAR(45) NULL, birthYear YEAR(4) NULL, deathYear YEAR(4) NULL, age INT NULL, movieNum INT NULL, PRIMARY KEY (nconst)); CREATE TABLE title_episode (episodeID VARCHAR(45) NOT NULL,parentTconst VARCHAR(45) NULL, seasonNumber INT NULL,episodeNumber INT NULL, PRIMARY KEY (episodeID), CONSTRAINT parentTconst FOREIGN KEY (parentTconst) REFERENCES title_basics (tconst) ON DELETE NO ACTION ON UPDATE NO ACTION); CREATE TABLE title_principals (tconst VARCHAR(45) NOT NULL, ordering INT NULL, nconst VARCHAR(45) NULL, category VARCHAR(45) NULL,job VARCHAR(45) NULL, characters VARCHAR(45) NOT NULL, PRIMARY KEY (tconst, characters), CONSTRAINT nconst FOREIGN KEY (nconst)REFERENCES name_basics (nconst) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT tconst FOREIGN KEY (tconst) REFERENCES title_basics (tconst) ON DELETE NO ACTION ON UPDATE NO ACTION); CREATE TABLE title_ratings…arrow_forwardPage - 1 DBMS - TASK 1: Draw an ERD and Map it to relational schema. Please complete reading page 1 before going to page 2. In this task, you will be given a text that describes a company (in page 2). Each student should complete the steps below to fully solve the task Represent the company description as an ER-diagram, including all relevant constraints as the following steps: - Each student should download Lucidchart app Every student should use Lucidchart to draw the ERD Edit Lucidchart Blank diagram Select View Insert Arrange Help 80 What's New Saved Start a free trial to access unlimited shapes and documents! X BIUAT- 2 px None None 408 Liberation Sans ▾ 10 pt + Shapes O Standard × T Flowchart 000 ☐ ODOT ☐ ☐ ☐ ☐ ☐☐ )>0 × TU Xx 14 << ΔΟ ▼ Shapes ΠΑ ☐ ☐ Name B Present MA when you create After each entity ID print screen, your name include your user ID should be shown university ID number Department 0190334 Dep_ID Employee 0190334 Namearrow_forward
arrow_back_ios
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