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

Systems Architecture

7th Edition

ISBN:9781305080195

Author:Stephen D. Burd

Publisher:Stephen D. Burd

Chapter3: Data Representation

Section: Chapter Questions

Problem 3RP

See similar textbooks

Similar questions

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
Software Engineering The recruitment agency TEMPO proposes internship employees for enterprises for a periodof time. An internship employee is a temporary employee that will be recruited for a periodof time. You must develop a database application in order to satisfy these specifications: The enterprises (id, name, address and phone number) define the posts for which theyneed one or many employees. A post is defined with a label, a daily remuneration, thestart date, the end date and the number of employees for this post. A post needseventually diploma(s) and a list of many skills. An internship employee is described by a ssn, first name, last name, an address, aphone number, his/her diplomas (we store for each diploma the year when it isobtained) and his/her skills. An employee could be recruited for one or many posts. This recruitment of anemployee for a given post is validated by a chief (ssn, first name, last name, address)designated by the enterprise. This chief is unique to an…
Course: Database Systems: Design and Application Web Shop scenario: A supplier (Supp-#) processes many purchase orders (PO-#) and sells a number of products (Prod-#). A product is sold by only one supplier. Each of the purchase orders that a supplier may process includes several products. The relationship between order and product is defined as "Line Item". A customer (Cust-#) may place one or more purchase orders. A purchase order may be placed by only one customer. Assignment requirements: Answer the following questions using above scenario: Identify the entities and attributes that required to draw the ER diagram. Integrate the entities with appropriate relationship and design the unified ER model.
Course Title: Object Oriented Analysis and Design Question : There is a utility company which provides waste management services to citizens. It has a CRM system, billing system, messaging system and mapping (GIS) system. When a customer is created in the CRM system, the billing, messaging, and GIS systems need a copy of customer data to perform follow-up tasks and automation. For instance—the billing system will create a billing account for the customer, the messaging system will send a welcome message with customer account details on preferred communication channel and the GIS system will update the customer’s address in the database for mapping purposes. After completion of one billing cycle the billing dept. sends a copy of the bill in a pdf format on the customer email address and the bill amount is directly debited from the customer’s bank account. Draw an appropriate Activity diagram . (Note): Forks and joins have the same notation: either a horizontal or vertical bar (the…
Abstract data types are forms of data that do not conform to any particular specifications.
Final Project By Java OOP A simple Library Management System aims at developing a computerized system to maintain all the day-to-day activity of a library. This functional project handles data of books and their copies, users that can be either students or teachers as well as book loans. Each book has an id, title, publisher, author, year, number of copies, copies of the book, financial value and status(true if at least one copy is available). Each copy has id, status whether a copy is available to be borrowed of not and insert date. A user has id, first name, last name, password and registration date. In addition to the total fine which is the total of each fine the user get for every late return of a book. Students has major whereas staff has department. When borrowing a book, the system recording the copy of the book being issued (borrowed), the borrower and the date at which borrowed. The system must first check for the copy availability and record the return date which is 3 days…
Entity Relationship Diagram (ERD) A salesperson may manage many other salespeople. A salesperson is managed by only one salespeople. A salesperson can be an agent for many customers. A customer is managed by one salespeople. A customer can place many orders. An order can be placed by one customer. An order lists many inventory items. An inventory item may be listed on many orders. An inventory item is assembled from many parts. A part may be assembled into many inventory items. Many employees assemble an inventory item from many parts. A supplier supplies many parts. A part may be supplied by many suppliers.
Q : Design an ERD for the following University System. Design ERD for the university where we have a database that stores information about student, degree program, course as well as the student’s semester if he took a particular course, also store marks with grad after completion the course, where there are one or more program offered in which we have one or more course in a program. It is necessary for a student to be enrolled in a program and it is the part of his program to take either course. A program has a name, a program identifier, the total credit points required to graduate, and the year it commenced. A course has a name, a course identifier, a credit point value, and the year it commenced. Students have one or more given names, a surname, a student identifier, a date of birth, and the year they first enrolled. When a student takes a course, the year and semester he attempted it are recorded. When he finishes the course, a grade (such as A or B) and a mark (such as 60…
Point of sales systemcreate a POS in java that will have the basic operation of a point of sales System.your system should provide the following:1. a login frame to allow the employee to login2. cash register sales3. items scanning when a client bring the product4. real time stock level5. stock infosl (product codes ,and informartion such as price and quantity)6. sales reportall the product must be stored in a SQL database using necessary security.Note:1. Use GUI to make the application2. Do not use any builder to drag and drop component
The objects in database description is called
Draw an ER diagram for the following car sharing system: In the car sharing system, a CarMatch application has record of anyone who would like to share his/her car, known as a CarSharer. An Administrator registers all the potential CarSharers and with their first name, last name, home address, and date of birth. Each CarSharer is also assigned a unique id. A CarSharer can take policies issued by the Insurance Company. Each policy has a number, premium, and a start date. A CarSharer needs to know the start and destination address of each Journey.
Database & Python ETL Provision one database of your choosing (SQL, NoSQL, Graph). Write a python ETL that ingests the provided data, transforms it in some way, and loads it into the database.