4. A database is built in the library to catalogue books and handle book lending. Each book in the library is catalogued by author. Any number of books can be associated to an author. Each user is allowed to borrow one and only one book. The database stores information about the following entities and their relationships: User: unique reference number, name, and phone number, ● Author: unique reference number, name ● Book: unique title, category (e.g. Physics, Science,..), publication year, the user (reference) borrowing the book, the author (reference) of the book. a) Write SQL CREATE TABLE statements for these three tables. Justify your choices of keys used. b) Write SQL statements to perform the following tasks: (i) Insert a new entry in the user table (for a set of values at your choice). (ii) List the title for all books with author "Stephen King". (iii) List the names of all the authors in the database, and beside each name give the number of books published on or after 2017

4. A database is built in the library to catalogue books and handle book lending. Each book in the library is catalogued by author. Any number of books can be associated to an author. Each user is allowed to borrow one and only one book. The database stores information about the following entities and their relationships: User: unique reference number, name, and phone number, ● Author: unique reference number, name ● Book: unique title, category (e.g. Physics, Science,..), publication year, the user (reference) borrowing the book, the author (reference) of the book. a) Write SQL CREATE TABLE statements for these three tables. Justify your choices of keys used. b) Write SQL statements to perform the following tasks: (i) Insert a new entry in the user table (for a set of values at your choice). (ii) List the title for all books with author "Stephen King". (iii) List the names of all the authors in the database, and beside each name give the number of books published on or after 2017

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: esign a RELATIONAL SCHEMA not an ERD for the pllowing A job-shop accounting system is part of an…

A: Here we describe all points:

Q: n an EER diagram for a database used to manage a racket sport association (for example, US Tennis…

A: ER diagram description: ER diagram indicates the relationship between one entity to any other…

Q: Design a database that can satisfy the following requirements: a. For each marina, list the number,…

A: a. To store the data of the marinas, create a table with marina number as primary key and rest of…

Q: In a normal context including a database, there need to be a minimum of nine unique issues to be…

A: Introduction: Data is stored, viewed, and updated using a straightforward interface by the DBMS…

Q: Case Study: National Football League (NFL) Suppose you are given the following requirements for a…

A: Objects (Entities) and Attributes:TeamsNameCityCoachCaptainPlayersNamePositionSkill LevelInjury…

Q: A multivalued dimension is a dimension that has many values

A: Answer: A "Multi Valued Dimension" is a dimension with more than 1 value for each reality line. As…

Q: Review: Entity-Relationship (ER) "crow-feet symbol" О Опе-to-many: one sale person can have many…

A: ER-Diagram:

Q: The problems for this chapter use a database for a simple department store that sells items to…

A: Actually, SQL is a structured query language which is used to modify, delete and update the data.

Q: Define class name? Define attributes name and color

A: Class name The class name is used as the name for the class. It is also used as the name for the…

Q: Q1) Draw the ER Diagram for the scenario below. The company database keeps track of company's…

A: The Entity-Relationship (ER) diagram provided above captures the essential elements of a company…

Q: Each department runs a number of courses. The university provides a set of modules used in different…

A: Find the attachment of the ER diagram Underlined words are primary key

Q: The attributes field of objects is a potential storage location for data. The qualities of the class…

Q: Regular entity type is transformed to a relation.

A: To be determine: Regular entity type is transformed to a relation.

Q: Design a database based on this information: Each pharmaceutical company is identified by name and…

A: Answer (a):

Q: Question 5I Consider the following database instance. Table name: Student Primary key: SID SID SNAME…

A: Q: Table Output when given commands are run

Q: DATABASE MANAGEMENT (N.B NO ATTRIBUTES TO BE DISPLAYED JUST ENTITIES CONNECTED USING RELATIONSHIP…

A: Given The answer is given below.

Q: Requirements About the System: A local library intends to modernize its operations and seeks your…

A: Hi.. As per my company rule i can answer the 1st three subparts. Below this you can find the use…

Q: The problems for this chapter use a database for a simple department store that sells items to…

A: Please find the solution below in step 2. if you have any follow-up questions, feel free to post…

Concept explainers

Types of database architectures

A Database Architecture represents the Database Management System’s (DBMS) design (schema). The DBMS architecture makes it easy to understand the components involved in the database system and their relations. The DBMS architecture may vary based on the system on which it runs. Some systems contain both client and server machines whereas some run only on a server machine. Based on the architecture, the system can be classified as centralized, decentralized, or hierarchical.

Question

4.
A database is built in the library to catalogue books and handle book lending.
Each book in the library is catalogued by author. Any number of books can be
associated to an author. Each user is allowed to borrow one and only one book.
The database stores information about the following entities and their
relationships:
User: unique reference number, name, and phone number,
Author: unique reference number, name
Book: unique title, category (e.g. Physics, Science,..), publication year, the
user (reference) borrowing the book, the author (reference) of the book.
a) Write SQL CREATE TABLE statements for these three tables. Justify
your choices of keys used.
b) Write SQL statements to perform the following tasks:
(i) Insert a new entry in the user table (for a set of values
at your choice).
(ii) List the title for all books with author "Stephen King".
(iii) List the names of all the authors in the database, and beside
each name give the number of books published on or after 2017

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

Step 1

VIEW

Step 2

VIEW

Step by step

Solved in 2 steps

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

Case Study: Database System for Teaching institution Assume you have been contracted by a teaching institution to develop a database systemto keep track of student registration and accommodation records.The institution courses are offered by instructors during physical classes. Depending onthe student’s IQ, there are no limitations to how many courses a student can enroll in.administrators are responsible for student accommodation. This institution owns anumber of rooms for accommodation and each student is given a shared room key afterenrollment. Each room has furniture attached to it. question- draw a single ER diagram to represent the data requirements for the project based on Chen Notation. use any Online editor to create the that supports Chen Notations to create the model?
Convert er diagram to Relational database
DATABASE SYSTEMS: QUESTION: A local businesswoman has decided to start her own Internet business, called Masterpieces Ltd, hiring paintings to private individuals and commercial companies. Because of your reputation as a database designer she has called upon your services to design and implement a database to support her new business. At the initial planning meeting, to discuss the design, the following user requirements were requested. The system must be able to manage the details of customers, paintings and those paintings currently on hire to customers. Customers are categorized as B (bronze), S (silver), G (gold) or P (platinum). These categories entitle a customer to a discount of 0%, 5%, 10% or 15% respectively. Customers often request paintings by a particular artist or theme (e.g. animal, landscape, seascape, naval, still‐life, etc). Over time a customer may hire the same painting more than once. Each painting is allocated a customer monthly rental price defined by the owner.…
Create an ER diagram for the following requirements:You are asked to design a database to store music album information. A simplified set of requirementsare provided below.- A music album has a title, year of release, total running time, one or more artists, and list ofsongs.- Each song has a lead artist and may have a list of additional artists. Each song has a list of otherassociated people such as mixer, mix engineer, keyboards, guitar, etc. Each song has a genre,running time, and lyrics. Each song can be associated with many albums, but needs to beassociated with at least one.- Each person has a first name, last name, and nickname.
In what ways does it matter what data type a field is set to be?
Part Design a database for an Access Control System. The purpose of the system is to control who has access to which doors at what time. There are six entities in this database: ● ● Users Groups O A user can be assigned to more than one group. Doors AccessRules O Decides which Group has access to which Door at which TimeSchedule. Example: Group A can access the Front Door on Monday from 7am to 9pm. o o Example: Group B can access the Back Door on Saturday from 8am to 10pm. TimeSchedules To make it simple, we only need to pport Weekly Schedules. The TimeSchedules table has the following columns: Id O O DayOfWeek StartTime ■ EndTime o Example: a schedule with DayOfWeek-Monday, StartTime=7:00, EndTime=23:00 means this schedule grants access on Monday from 7am to 11pm. Entrance Logs O This table keeps a permanent record of which user accessed which door at what time. o The logs must remain valid even after the related records have been updated. For example, after user X is deleted, the…
“Martial Arts R Us” (MARU) needs a database. MARU is a martial arts school with hundreds of students. The database must keep track of all the classes that are offered, who is assigned to teach each class, and which students attend each class. Also, it is important to track the progress of each student as they advance. Students are given a student number when they join the school. The number is stored along with their name, date of birth, and the date they joined the school. All instructors are also students, but clearly not all students are instructors. In addition to the normal student information, for all instructors, the date that they start working as an instructor must be recorded along with their instructor status (compensated or volunteer). An instructor may be assigned to teach any number of classes, but each class has one and only one assigned instructor. Some instructors, especially volunteer instructors, may not be assigned to any class. A class is offered for a…
Module 01 Introduction to Database Management Currently, the class price and other fees reside in the Registration table. If Pitt Fitness practices dynamic pricing (the management changes the prices as the classes fill up as the airlines do), should they put the class price in the Classes table? Why or why not?Entities:1. Classes (ClassID, ClassName, Room, Location, LengthofTime, MaxSize, TypeofClass, Day, Time, Description)2. ClassInstructors (ClassID, InstructorID)3. Customers (CustomerID, LastName, FirstName, StreetAddress, City, State, ZipCode, EmailAddress, PhoneNumber, BirthDate)4. Instructors (InstructorID, InstructorLastName, InstructorFirstName, InstructorStreetAddress, InstructorCity, InstructorState, InstructorZipCode, InstructorEmailAddress, InstructorPhoneNumber)5. Reservations (ReservationID, ClassID, ClassDate, ClassPrice, OtherFees, CustomerID)
A cormpany has a single table in a computer (e.g., a spreadsheet) in which is stored every information abow each worker including the transactions he/she does in the company. This,table is A A table database B A spreadsheet CA flat file database •D A joint database
Computer Science It is needed to design a database system for a pharmacy. The pharmacy contains many different items. Items may be products or medicines. Medicines are classified into different types either bills, injection, capsule or drugs or any combination of them. An item must be supplied by only one supplier. A supplier may supply many type of items. A customer may order many items. The pharmacy has a unique name, address and more than one telephone. The doctor can prescribe one or many medication, injections can not be bought without a doctor prescription. You are requested to draw the EERD diagram for the described system You are required to write down any assumptions made from your side You should add the attributes and cardinality for all the entities You are required to model the EERD into relational model
Computer Science Relational Model (Logical and Physical Schema Development) The conceptual model developed in phase 2 will be transformed into a logical and physical schema using the relational data model. The outcome of this transformation will be a relational schema. The schema should have the following components: Set of required relations All the integrity constraints Submission and Evaluation: A report consisting of the problem statement, ER diagram, relational schema And implement the database using SQL should include: All the tables properly defined /created All constraints properly defined For each table, few instances of data should be entered.
A bank requires a database designed to keep track of its customers accounts. Each customer may have a number of accounts. Each of these accounts may be in the name of one or more customers. Each account is located in a particular branch of a bank. In addition, each customer may have zero or more loans with the bank. Each loan must be with one or more customers. Each loan is also located in a particular branch. The database must record the following information: The name of the bank, its address and its main telephone number The address and the sort code for each branch The name of each customer, the customers address and the customers telephone number For each bank account: the account number, its balance and the type of the account For each loan: the loan account number, the outstanding balance and the interest rate The database must also record the various relations between the different entities in the database Draw an entity relationship diagram for the database. In your diagram,…