qbe-in-class-practice

32. In a relational model, relations are termed as a. Tuples b. Attributes c. Rows d. Tables

Material : Relational Algebra Course : Database Systems Create a Relational Algebra From Entity Relational Diagram (ERD) Below for:a) Displays books published in the last 5 years from 2020.b) Displays details of customers who have done shopping (using the Join operator)c) Displays email customers who have done shopping (using the Set operator)d) Displays the ISBN book that has never been purchased (using the Set operator)

Database Schema The schema for the Ch07_FACT database is shown below and should be used to answer the next several problems. Click this image to view it in its own tab. FIGURE P7.56 THE CH07_FACT ERD CHECKOUT PATRON PK Check Num PK Pat ID FK1 Book_Num FK2 Pat_ID Check_Out_Date Check_Due_Date Check_In_Date >0-----H- Pat_FName Pat LName Pat_Type BOOK AUTHOR PK Book_Num PK Au ID Book_Title Book_Year Book_Cost Book_Subject FK1 Pat_ID Au_FName Au_LName Au_BirthYear WRITES PK,FK1 Book Num PK,FK2 Au ID The CIS Department at Tiny College maintains the Free Access to Current Technology (FACT) library of e-books. FACT is a collection of current technology e-books for use by faculty and students. Agreements with the publishers allow patrons to electronically check out a book,

SQL help

Q2/ Map your ER into a relational schema. All constraints must be included: Primary keys (PK) highlighted, foreign keys (FK) well referring to their PK, unique not null and other constraints also specified. Name PUBLISHER Phone Address publish Author_id Book_id M BOOK AUTHOR Has Titele M Name Date_out Loan Due_date phone f name I name BORROWER address Card id F_name

Q1: Explain result of questions below when you have library schema as described in the tables. (Answer five only) Book tb BOOK idTR le Author tb BOOK id Author name Publis Henry F Korth Andrew Hun Oliver Theobald Daniel Cohen Anil Kumar her id 9123 9123 Database Systems 254 9124 Software Engineering Machine Learning 9124 191 9125 9125 110 9126 Computer Theory Basic of Digital logic 9126 254 9127 9127 110 Publisher_tb Publisher id name Country phone Address Hachette Livre. Macmillan 254 USA +125862221 191 Germany +48517778 110 Simon & Schuster. USA +125117199 1- Insert into Book tb. 2- Insert jinto Publisher_tb.T 3- Delete the Author Name with Book_id = '9162', 4- insert into Book_tb. 5- Delete the Author Name with Book_id = '9126'. 6- Modify Book_id for Author name 'Andrew Hun' to 9121. 7. Modify Publisher name Macmillan to Mac_millan and Publisher id to 192 with cascaded constraint

SQL Help

Given the following Theatre Database form:    No.of seats:500                          Owner: J.Martin Year Founded: 1890 Owner’s Phone: 2347756 Play Name Play Type No. of performances Play Author name Play Author nationality MacBeth Tragedy 35 W.shakesper English Ulysses Drama 12 J.Joyce Irish               Represent the above as an unnormalized relational schema then apply the rules of normalization to normalize it to 3NF.

Question 1 A city college would like to maintain their academic information in a MySql database system.  The following are the interested information: Courses: Catalog # (CIT 170 for instance) Credit Hours; Name of the Course (Database Design Fundamentals for instance) Course Descriptions Prerequisite Components (Lecture, or Laboratory for instance) Classes: Class # Class Name (usually the course name, M 170 for instance) Start Date End Date Meeting time (9:30 am ~ 12:15 pm, for instance) Days in a week (Monday) Term (Spring 2018, for instance) Location (TIE Building for instance) Room (UB 301, for instance) Instructor Online status (online, or in-person) Faculty: Employee Id (001880301, for instance) First Name Last Name Title (professor, for instance) Email Phone Office Location (TIE building, for instance) Room (UB303, for instance) Student: Student ID First Name Last Name Plan Sub Plan Advisor Current GPA Grade of each course The Term you got the grade for a course. The…

QUESTION 1 SQL: Consider the following database schema for flight reservation database.  A flight can be booked by many passengers, and a passenger can book different flights. For each booking, the date of the flight and price are recorded. The database schema is given below   Flight (FID:int, Airline:string(unique), DepartingAirport:string, ArrivalAirport:string, Rating: int (1,2,3,4,5) ) Booking (FlightID: int, PassengerCPR: int, Date: date, Time: time, Price: double) Passenger (CPR:int, FirstName: String, LastName: String, Address:String) Write SQL Code to:   Create the Flight Table.List the Departing Airport and Arrival Airport for all flights with rating 4 or 5.

Build a relational model for the following ER schema

Q1) Draw the Relational Schema for the relationship(s) between EMPLOYEE and STORE.

Relation algebra (Database)

Consider the relational schema for a library system as given below: Member (mem_no, name, dob) Books (isbn, title, authors, publisher) Borrowed (mem_no, isbn, date) Write the following queries in relational algebra. i. List the members who have not borrowed any books from the library. ii. Name all the books borrowed by a member named 'Ram'. ii. List the book that has been borrowed the most.

relational algebra

Relational model: In the personal model, data are prearranged in tables, which contain of table. A data in a table is called fields. Fields in a db table are also referred as qualities of a db object. A group of related fields are called as a record, which I also called as db object or entity. A group of records are called a table. Keys in a relational db table refer to a key field. To identify each record in a table, there must be at least one data field that should be unique. That is, a corresponding data field of every record should be unique. Therefore, key field is used to identify the unique record from a collection of records.

Normalization of the relational model up to the 3rd Normal Form;

For the following Student table, find all the possible functional dependencies that arehe ld.Schema: Student (Regno, Name, DOB, Phone, Instructor_ID, Instructor_Name, Instructor_Office)Gender, Course_ID, Course_Na me ,Regno Name 14M01 Kumar 14M05 Mary 14M07 Ram 14M01 Kumar 14B01 Revathi 14M09 Steve 14B03 RamyaDOB 12-Jan-1996 10-Jun-1995 10-May-1996 12-Jan-1996 10-Dec-1995 23-Oct-1995 20-Jul-1996Phone 12345 12367 12898 12345 23456 34567 23456Gender CID M C1 F C1 M C1 M C3 F C3 M C4 F C4CNam

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. Producer Pharmacy medicines The pharmacy contains employees, certain medicines customers and patients Date Name Customers age pharmacy sex employees Commercial Record Address Location Salary Name

Q4: Consider the following MAILORDER relational schema describing the data for a mail order company. (Choose five only). (10 Marks) PARTS(Pno, Pname, Qoh, Price, Olevel) CUSTOMERS(Cno, Cname, Street, Zip, Phone) EMPLOYEES(Eno, Ename, Zip, Hdate) ZIP CODES(Zip, City) ORDERS(Ono, Cno, Eno, Received, Shipped) ODETAILS(Ono, Pno, Qty) Qoh stands for quantity on hand: the other attribute names are self-explanatory. Specify and execute the following queries using the RA interpreter on the MAILORDER database schema. a. Retrieve the names of parts that cost less than $20.00. b. Retrieve the names and cities of employees who have taken orders for parts costing more than $50.00. c. Retrieve the pairs of customer number values of customers who live in the same ZIP Code. d. Retrieve the names of customers who have ordered parts from employees living in Wichita. e. Retrieve the names of customers who have ordered parts costing less than$20.00. f. Retrieve the names of customers who have not placed…

97. The column header of table in relational model terminology is classified as a. attribute b. table starter c. table ender d. entity domain

For the following relational model: Student(sid, fname, lname, gpa)Course ( cid, code, name, credithours)Enrolled (sid, cid,grade) Write SQL queries for the following: Print all the students by the name order desc by the gpa. Print all the computer sciences courses, these courses with code start with ‘CS’sorted alphabetically. Print all the students with a gpa less than 3.0 Print all the students with a gpa of a least 3.0 Find all students that have enrolled in database course Find all students that have enrolled in database and network courses Find all the students that have (A or more) at least once

ER Mapping : Convert the ER diagram into a relational database schema. For each relation, specify the primary keys and the foreign key(s) if any (50 pts) Upload you Relational Diagram here BANK BRANCHES BANK-BRANCH Code Name Addr Addr BranchNo АССTS LOANS N AcctNo Balance LoanNo Amount ACCOUNT Туре LOAN Type M M A-C L-C N Name N. SSN Phone CUSTOMER Addr

need to create the ERD for this

,,,,,,,

ITCS285