Question 5I Consider the following database instance. Table name: Student Primary key: SID SID SNAME Fred 1. Mack 3. Shell Table name: Course Primary key: CID CID CNAME Finance 2. CIS Table name: Register Primary key: SID,CID 2)
Q: Q2/ Consider the relational database of Figure 1. What are the appropriate primary keys? employee…
A: Answer: The Primary Key is used to uniquely identify each record in a table. Primary key cannot have…
Q: Consider the following two relations for Millennium College:STUDENT(StudentiD…
A: A)StudentID in STUDENT_TBecause it is a primary key and the index would enforce uniqueness of the…
Q: Draw a customer-input screen for a utility company. What information should be captured? In what…
A: Solution :: Some involve advances in basic hidden (under) display and interface technologies…
Q: Consider the relational database of Figure 1. What are the appropriate primary keys? employee…
A: Given query is employee (person_name, street, city) works (person_name, company_name, salary)…
Q: 9. How many types of keys in Database Design? a. Candidate key b. Primary key c. Foreign key d. All…
A: There are Seven Types of Database design keys, they are Super, Primary, Candidate, Alternate,…
Q: Which of the following is NOT characteristic of a relational database model? A Tables B Tree like…
A: The correct answer is: B. Tree like structure
Q: 4. Show the result of the following queries as it would apply to the database state given above. 1.…
A: All distinct student names from Student table whose Major is CS are displayed Name Smith…
Q: Consider the database schema below for cities in countries. A country have many cities, and the same…
A: Domain Constraint: It specifies that the value taken b the attributes in a relation must contain an…
Q: View 2 Seneca Courses Inc. Product Spreadsheet All the course books are maintained on the…
A: Actually, the answer has given below:
Q: Question#3 Consider the following relations for a database that keeps track of items supplied by…
A: We are given two database tables: Vendor and Item and we are asked about the Degree, cardinality,…
Q: Icus FRAME Question 7 Consider the following Database Schema. CUSTOMR IYOICE PK CUA CODE LNE PK…
A: we need to provide SQL query to determine how many products have a price that is less than $10.
Q: Consider the database for a company named WPC that contains data about company employees,…
A: According to the Question below the Solution:
Q: Consider a database with the following relations: Employee (Employee ID, Name, salary, startDate)…
A: The following SQL Query can be broken down into sub-queries: Sub-Query 1: Display Employees by…
Q: QUESTION 22 When identifying functional dependencies, the sequence/ordering of columns in a database…
A: Answer question 22 is given below
Q: Consider the following database schema designed to store information about Books, and Chapters. Book…
A: Here, I have to write queries for the above question.
Q: Supposing we have the following database schema: Emp(SSN,Name, position, Dept) Dept(code,name,…
A: Answer:- select Dept, count(SSN) from from EMP group by Dept;
Q: Being a database expert create fragments of the given relation(table) “student” on the basis of the…
A: NOTE: Based on our rules, when multi-part questions are posted, we should answer only the first…
Q: Exercise 1-2 A small library keeps a roster of who will be at the desk each day. They have a…
A: Answer :- This database can only ever be as good as the parameters that are entered, and some…
Q: Q3/ Consider the relational database of Figure 1. What are the appropriate primary keys? employee…
A: Answer Here most appropriately in relation employee person_name can be set s a primary key, because…
Q: QUESTION 4 Convert the following ER diagram into a relational database schema, be certain to…
A: Convert ER diagram into relational database schema.
Q: Given the Bank database that contains the following tables (primary keys are underlined and foreign…
A: According to the given question we just need to create the table Agency to fetch the data…
Q: (4) Consider the following two relations in a database: CUSTOMERS: customer_id last_name first_name…
A: Answer: Answer a: customer_id order_id order_date 4000 4 2016/04/20 5000 2 2016/04/18 7000…
Q: 41.What defines a transitive dependency in a database table
A: 41.Defined a transitive dependency in a database table
Q: Example 2.1) (Answer according to Figure 1) Convert the data model in Figure 1 to Database Design…
A: CREATE TABLE CUSTOMER( Customer ID integer primarykey, Name char(20) notnull, Surname char(15)…
Q: A database containing data about parking violations contains no transitive dependencies. Which of…
A: I have given an answer in step 2.
Q: What types of additional information and constraints would you like to represent in the schema
A: Given Data: (SSN INT NOT NULL, FNAME VARCHAR2(20), M VARCHAR(1),…
Q: Consider the following database schema. BOOK (BID, TITLE, GENRE, AUTHOR) BORROWS (RID, BID, DATE)…
A: Given database schema: -…
Q: A local hotel invites you to improve their database system. They only have one large table with the…
A: Normalization:- Normalization is the process of minimizing redundancy and organizing data to avoid…
Q: ubject - Fundamentals to Database Question 5 Based on the Table 5 and Table 6, write the SQL…
A: Note:- you can use yyyy for 2013 and yy for 13. Use like operator to get names in pattern (name…
Q: QUESTION 6 Meta-Data is a subset of the database that contains some information of user interest O…
A: Metadata is also a data Metadata provides information on other data. Refer step 2 for the answer.
Q: CREATE the following table on mySQL Consider the following database instance. Table name: Students…
A: Here have to determine sql statement for given problem.
Q: QUESTION 3 Consider the database schema below: Company (CompnayID: integer, CompnayName:…
A: Integrity constraint violations occur when an insert, update, or delete statement violates a primary…
Q: Consider the following schema: Sailors(sid, sname, rating, age) Reserve(sid, bid, day) Boats(bid,…
A: ans is given in below
Q: Consider the following normalized relations from a database in a large retail chain What…
A: Denormalisation is process related to the database. The primary requirement to do denormalisation is…
Q: Consider the following two relations for Millennium College: STUDENT(StudentID, StudentName,…
A: “Since you have posted a question with multiple sub-parts, we will solve first subpart for you. To…
Q: Consider the Computing equipment database schema defined as follows. Product (maker, model, type) PC…
A: a)R1 := σ type = "Laptop"(Maker) R2 := σ type = "Pc"(Maker) R3 := R1-R2 R4 := π maker(R3)b)R1 :=…
Q: The database used for this question is a very simple one with the following schema: (Primary keys…
A: Hey, since there are multiple sub parts questions posted, we will answer first three question. If…
Q: A local hotel invites you to improve their database system. They only have one large table with the…
A: Normalization:- Normalization is the process of minimizing redundancy and organizing data to avoid…
Q: Consider the following ORDER-PROCESSING database schema: Customer (CustNo, Cname, City) Item…
A: No of entities in the ERD are: 1. Customer entity 2. Order entity 3. Item entity 4. Sales entity 5.…
Q: Consider the database schema below and answer the questions that follow using SQL.…
A: 1. Retrieve all employees in “sales department” whose salary is between Ksh 30,000 and Ksh…
Q: 5. Snapshot of the dta in the database at a given instant of time is called a. Database Schema b.…
A: please see the next step for solution
Q: me CHAR(20), meets atTIME, room CHAR(10), fid INTEGER, PRIMARY KEY (name), FOREIGN KEY (fid)…
A: Given the following database schema: CREATE TABLE Student ( snum INTEGER, sname CHAR(20), major…
Q: For each table, identify the primary key and the foreign key(s). If a table does not have a foreign…
A: As per our policy, "Since you have asked multiple questions, we will solve the first question for…
Q: The database used for this question is a very simple one with the following schema: (Primary keys…
A: a. We have selected the first name and last name attribute using project symbol, City named Perth…
Q: Subject - Fundamentals to Database Question 2 Based on the Table 5 and Table 6, write the SQL…
A: Use delete query to remove rows from tables. To udpate column data use update query order by…
Q: (4) Consider the following two relations in a database: CUSTOMERS: customer_id last_name first_name…
A: SQL is a language for interacting with databases. It is the basic language for relational database…
Q: Map the database schema shown in the following figure into a relational database schema. grade…
A: Given ER diagram contains 5 entities: Class Course Student Instructor Department After converting…
Q: Create a MYSQL database table named Employees as the following. ID Frist_name Last_name Department…
A: Database CREATE TABLE EMPLOYEES (ID INT, FIRST_NAME VARCHAR(10) , LAST_NAME VATCHAR(10), DEPARTMENT…
Step by step
Solved in 2 steps
- Write a SQL trigger to carryout the following action: If an account is deleted, then write a trigger to delete thedependent tuple(s) from the depositor table for every owner of the deleted account. Notethat there may be jointly-owned bank accounts. In other words, you are to write a triggerthat performs the exact action of an ON DELETE CASCADE clause of a FOREIGNKEY CONSTRAINT. You must submit both your trigger function definition, and yourtrigger definition(d) Write an SQL statement, using the inner join construct, that will, for the lecturer John Smith, list in alphabetical order the students taking any module which he teaches. (e) What information does the output from the following SQL query provide? SELECT Lecturer, COUNT(DISTINCT Student) FROM class INNER JOIN staff ON class.Module = staff.Module WHERE Grade < 40 GROUP BY Lecturer; (f) Write an SQL query which would obtain the following information: for each lecturer, what is the average grade obtained by students across all of the modules which they have taught?Given the following relational database schema: Student = (SSN, Name, Major) Course = ( CourseNumber, PrerequisiteCourseNumber, Course Title, NumberUnits) Section = ( CourseNumber, Quarter, RoomNumber, DayTime), where DayTime is of the form MW 1:00-2:00PM. Enrollment = (SSN,CourseNumber, Quarter, Grade)// Grade is either Null or a letter grade. Express the following queries using appropriate SQL statements with a minimum number of operations: (a) List the name, SSN and number of courses the student has taken and completed, i.e. received a letter grade (courses with the same CourseNumber taken in different quarters are counted as different courses). (b) List the name, SSN and number of courses the student has taken and completed with a letter grade C or better (courses with the same CourseNumber taken in different quarters are counted as different courses). (c) List the Course Title and CourseNumber which does not have any prerequisite. (d) List the name of every student and SSN who…
- Exercise 3: Procedural SQLWrite the following PSM functions or proceduresa) Take a price as argument and return the model number of the PC whose price isclosest.b) Take a maker and model as arguments, and return the price of whatever type ofproduct that model is.The following SQL for a Point of Sale database for a grocery store is not properly constructed. As result, when student attempts to populate the tables, it gives a foreign key constraint fail error. Modify the SQL so that it models the depicted schema and follows the following constraints: They is a one-to-many relationship between the salesdetails.SalesNumber andsales.SalesNumber fields. There is a one-to-many relationship between the products.prodid and salesdetails.prodid fields. There is to be a delete constraint on the product.prodid field a record in the product table should not be allowed to be deleted if they exist a matching prodid in the salesdetails table. Primary and foreign keys should be implemented in the appropriate tables. Referential Integrity should be enforce at the database level, where appropriate for example, if a sales record is deleted all associated records in the saledetails table should be deleted as well, etc. CREATE TABLE `products` ( `number`…51. The given SQL statement is intended to either (1) list the customer numbers, customers names, and headquarter cities of customers headquartered in Los Angeles or (2) satisfy the two conditions of having a customer number higher than 1000 and being headquartered in Denver. SELECT CUSTNUM, CUSTNAME, HQCITY FROM CUSTOMER WHERE HQCITY= 'Los Angeles" Which set of lines should be added to the end of the statement to make the statement work as intended? A: (CUSTNUM>1000 AND HQCITY-'Denver'); B: AND (CUSTNUM>1000 AND HQCITY='Denver*); C:OR CUSTNUM>1000 OR HQCITY='Denver": D:OR (CUSTNUM> 1000 AND HQCITY='Denver*);
- Just return SQL code/query with proper functions for each of the following. No output needed. Database Schema: Beers(name, manf)Bars(name,addr,license)Drinkers(name,addr,phone)Likes(drinker, beer)Sells(bar,beer,price)Frequents(drinker, bar) 1. Find all distinct drinkers whose phone numbers come from area code 917 and who like Budweiser or Bud (synonim!) 2. What beers does Mike like? 3. Which town has the most drinkers? 4. What bars are frequented by drinkers from that town (3)? 5. Provide all bars which serve beers that Mike likes 6. Who likes at least one same beer that Joe or Mike like? 7. All bars which sell at least one beer which is liked by at least one drinker who frequents these bars 8. Drinkers who like some beers sold by Caravan bar 9. Bars which sell Budweiser and are frequented by some drinkers who like Budweiser 10. Bars which are frequented by Mike and Steve 11. Drinker who like at least two beers that Mike likes 12. Bars which sell at least 3 beers that Mike likes (do…Using the sample database, create the Relational Algebra expression to answer the following queries. The database schema is as follows: Cars (c_id:number, c_brand:string, color:string, c_condition:string) Dealers (d_id:number, d_name:string, d_location:string) Inventory (c_id:number, d_id:number, costpercar:number, instock:number) Note: Inventory represents cars sold by dealers. How would I write a database queries for the following? 1. Find the names of Dealers who sell some Black Car 2. Find the d_id of Dealers who sell some Black or Silver Car 3. Find the d_ids of Dealers who sell some Black Car or are in Miami 4. Find the d_ids of Dealers who sell some Black Car and some Yellow Car 5. Find the d_ids of Dealers who sell every Black Car 6. Find the d_id of Dealers who sell used Cars that are BlackClosely examine the following tables and use it to answer the questions that follow:departmentstudent_type student_categorycourseStudentregistration registration_typeSuppose that the following are true:- all id column are primary keys in whichever table they are- the dept_id in course table is foreign key that is referencing the id column in the department table- the foreign keys stud_type_id, stud_cat_id in the student table are referencing the id columnsin the student_type and student_category tables respectively.- the coursecode and stud_id columns in the registration table are foreign keys in the courseand student tables respectively.1. Design an Entity Relationship Diagram for the system above.2. Write query that would return the names and contacts of all Local students.3. Write a query that would return names, contact and locations of all departments that has alocation text with Ave 3 as part of it. [hint: use the LIKE clause with “_” or “%”]4. What would be the result returned…
- Please answer the following in SQL: Find all pairs of customers who have purchased the exact same combination of cookie flavors. For example, customers with ID 1 and 10 have each purchased at least one Marzipan cookie and neither customer has purchased any other flavor of cookie. Report each pair of customers just once, sort by the numerically lower customer ID. The MySQL-specific GROUP_CONCAT and JSON_ARRAYAGG functions are not permitted. customers dataset CId: unique identifier of the customerLastName: last name of the customerFirstName: first name of the customer goods dataset GId: unique identifier of the baked goodFlavor: flavor/type of the good (e.g., "chocolate", "lemon") Food: category of the good (e.g., "cake", "tart")Price: price (in dollars) items dataset Receipt : receipt number Ordinal : position of the purchased item on the receipts. (i.e., first purchased item, second purchased item, etc...)Item : identifier of the item purchased (see…a) from university database , Write the following queries in SQL Find the titles of courses in the Comp. Sci. department that have 3 credits Find all instructors earning the highest salary (there may be more than one with the same salary) Find all courses that have never been offered (i.e., do not occur in the section relation). b) given the following tables( instructor, teaches) what is the output of the following query select i.name from instructor as i, teaches as t where i.ID=t.ID and semester=’ Fall’ and year=2017;Q1) 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 live in baghdad and work in ministery of foreign affairs And older than all the employees who work in the consuls department. 2- Find the name of employees who work as a counselor in ministries that have more than 5 departments . 3- Insert a new employee (o the database.