Table Store_Information store_name Sales Date Los Angeles $1500 Jan-05-1999 San Diego $250 Jan-07-1999 Los Angeles $300 Jan-08-1999 Boston $700 Jan-08-1999 Table Geography region_name store name East Boston East New York West Los Angeles West San Diego and we want to find out sales by region. We see that table Geography includes information on regions and stores, and table Store_Information contains sales information for each store. To get the sales information by region, we have to combine the information from the two tables.
Q: Q2/ Consider the following table is used to store contact information: Name Company Address Phonet…
A: The table is not normalized because there are 3 phone numbers for Joe and 2 phone numbers for Chris.…
Q: Rstudio Data Analytics Based on the above information, perform the following tasks: US gun murders…
A: Answer 4) in R we can access one variable at a time.if we want to multiple variables at once ,so we…
Q: Based on your start schema in a. , write a SQL query displaying the pet's name, pet type, treatment…
A: Write a SQL query displaying the pet's name, pet type, treatment name, visit day and visit fees for…
Q: II name(o dept_name ="Physics" (instructon)
A: In relational algebra expression, the SELECT operation (σ) is used to select a subset of the tuples…
Q: List of products names for a given list of vendor IDs:
A: As per our company guidelines, we are supposed to answer only one question per post and kindly…
Q: Q5) what is the normalization of the following table, where the primay keys are underlined: Product…
A: Find the required answer with explanation given as below :
Q: Figure Q3.13 The Ch03_VENDINGCO database tables Table Name: BOOTH BOOTH _PRODUCT BOOTH…
A: The relational algebra formula to apply an INTERSECT relational operator to the given tables is as…
Q: CIS111 Chapter 5 Lab Problems 3. Display the vendor name, number of invoices, average and total…
A: The following example shows three code examples. This first code example returns all rows (no WHERE…
Q: Finally, the transactions of the rides are stored in the TRAVELS table. For each travel, start and…
A: Structured Query Language : SQL is a space explicit language utilized in programming and intended…
Q: Q1: Map the following ER diagram into a relational schema. Date Time_stamp SHIP_MOVEMENT Time…
A: SHIP(Sname(PK),Owner,SHIP_AT_PORT(FK OF PORT PNAME),HOME_PORT(FK OF PORT PNAME),TYPE(FK…
Q: a) Put the following relation in BCNF: STUDENT_ACTIVITY STUDENT_ACTIVITY (StudentID, StudentName,…
A: please see the next step for solution
Q: Q2/ Consider the following table is used to store contact information: Name Company Address Phone1…
A: The table is not normalized because there are 3 phone numbers for Joe and 2 phone numbers for Chris.…
Q: If the functional dependencies are consistent with the above relation, put TRUE. If they are not,…
A: functionally dependent defines the relationship between attributes, how they are related. A-> B…
Q: Normalize the following form into UNF 1NF, 2NF and 3NF. Show primary keys and foreign keys for all…
A: We have given a table and we need to normalize the given table into 1NF, 2NF, and 3NF. Then we need…
Q: Give an example of how (Tables) can be related through (Keys).
A: In Relational database, it contains many tables which consists of columns and follow some rules like…
Q: 5. Convert the following ERD to a relational model. SEATING RTABLE Seating ID Uses RTable Nbr Nbr of…
A: ERD(Entity Relationship Diagram) An entity relationship diagram (ERD), also known as an entity…
Q: Transform the following E-R diagram-into relations.1 Hotels Rooms Hotel ID Room Number. Zipcode Room…
A: The given problem has an Entity Relationship (ER) model diagram and the requirement is to create the…
Q: 17. Create a basic ERD for the given tables: Table name: CUSTOMER Table name: INVOICE CUST NUM CUST…
A: An entity relationship model portrays interrelated things of interest in a particular space of…
Q: Consider the following Database Schema. CUSTOMER INVOICE LINE PK CUS CODE Sererates PK INY HUMBER…
A: Assuming "claw hammer" is stored in P_DESCRIPT in product Table. As all tables are connected, we…
Q: Convert the following ERD to a Relational model STREET CITY H_NAME COUNTRY DATE M REQUEST RESERVE…
A: An ERD is known as the entity relationship diagram which is also known as the entity relationship…
Q: (CLO-4] Use the given Student Table to answer the question below: Graduation Date Major GPA Student…
A: Given SQL query: SELECT Student_Name FROM Student_Database WHERE GPA>=2.5 AND GPA<=3.5 This…
Q: Table Schema: Amazon_Item (P#, P_NAME, SELL_PRICE, QTY) Amazon_Supply (S#*, P#*, SUP_PRICE)…
A: We have to develop a SQL code to find the find the supplier number, supplier name and their minimum…
Q: Suppose we have two tables. One table is Supplier with column supplieriD and ItemiD. The other table…
A: Here there are multiple questions so I have answered 1st questions below. If you want other…
Q: Use the following un-normalized data and prepare a set of tabled that is in its third normal form.…
A: Objective: Given a relation as follows, SO# Date Item# Description QTY Unit price Cust # Cust…
Q: The following three tables make up a simple reservation system for a small campground. The database…
A: ER Diagram: ER diagram is defined as the interconnection of entities and attributes how they are…
Q: Question 7: Build a table to show the frequency of each offence category. Arrange the frequency in…
A: Note: since your question contain multiple but we can answer only one question at a time due to our…
Q: Based on the EER Diagram given, perform the process of normalization from UNF until 3NF. Item Member…
A: Answer: I have explained in brief.
Q: Below is a sample data for Parts and for Vendors who supply those parts. In discussing these data…
A: Actually, given information is: uniquely identify parts and that vendor names uniquely identify…
Q: Which query in this relational database (represented by relational diagram) will provide you with…
A: The query database feature is as important as data storage capability. Hence, a variety of query…
Q: Use the XYZ Apartment Rental Company ERD shown on Figure 1 to answer questions 9 through 12:…
A: In the given ER diagram , We have three entities BUILDING, BUILDER and a Weak entity APARTMENT. For…
Q: Normalize the following schema up to third normal form by showing 1NF, 2NF and 3NF. DepartmentID…
A: We need to create 1NF, 2NF and 3NF normal forms for the given data table.
Q: QUESTION 2 Hillar Furniture Enterprise is a furniture store owned by Dr. Hillar Addo of AIT. The…
A: a)Following are the functional dependencies: If we know the materials_id, we can know material_name,…
Q: QUESTION 8 Answer All the questions: Consider the below Student table and write the appropriate…
A: Relational algebra query to display the details of students who study programming course and also…
Q: Consider the following two relational database tables to answer all the below questions. Smart Phone…
A: 3)sales table has four columns namely sales _ID,phone_ID,sales price,date of sale .sales ID can…
Q: 20 Suppose we have tables STAFF (StaffID, SName, Address, ProjID) and the data in the table is as…
A: Primary key is the first key which is used to identify one and only one instance of an entity…
Q: CUSTOMER PARENT ADT Data Operations Customer ID (preferably int, auto-generated) Name [1] Add…
A: #include<iostream>#include<string>#include<vector>#include<bits/stdc++.h>usi…
Q: Figure Q3.13 The Ch03_VENDINGCO database tables Table Name: BOOTH BOOTH _PRODUCT BOOTH…
A: In the given problem, two tables are given, and we need to create the table that results from…
Q: The materials manager at Tricky Pee Furniture Company maintains a list of suppliers for each of the…
A: We need to create table and fill sample data for the given schema.
Q: Models Airlines Fleet manufacturer model capacity Воeing Вoeing Вoeing MID AID AID MID Acgunt name 1…
A: We have three relational tables Models(MID,manufacturer,model,capacity) Airlines(AID,name)…
Q: A table called "game1" contains three columns and twenty rows, but "game2 has the same column as…
A: Tip It is a list of data linked together and stored in a database in the form of a table.As in…
Q: Given the following tables: DOG Dog Number 101 102 103 Dog Name Rex Pete Dog Age 3 11 8 Dog Breed…
A: Equijoin compare the attribute of two table on their equal values. But equijoin give the less…
Q: A relation FarmVisit is defined below with attributes in the table headings. FarmVisit visitorID…
A: answers for all the subsections is given below.
Q: The following three tables make up a simple reservation system for a small campground. The database…
A: A primary key is a key whichever uniquely identifies. Foreign key is column or group of columns that…
Q: The following question is based upon the Patient-Treatment relation which records the details of…
A: Step 1:- Note:- I am giving the solution to the 1 question only. The given question contains the…
Q: The relational database below contains details about Keretapi Tanah Melayu Berhad (KTMB) train…
A: The count( ) is used to get the count of unique station distinct keyword is used to return unique…
Q: Create a table “PRODUCTS” with the below mentioned structure: Product ID NUMBER(11) Supplier ID…
A: Create table PRODUCTS (Product_ID NUMBER(11),Supplier_ID NUMBER(11),Category_ID…
Q: A table called "game1" contains three columns and twenty rows, but "game2" has the same column as…
A: A table is a collection of related data recorded in a table format in a database. In relational and…
Q: ily_nam
A: Given as, customers - id - name - family_name - address - phone products code name…
Q: Instructions: Based from the given data dictionary below, answer the following questions: DATABASE…
A: Entities are the real world objects. Attributes are the characteristics. Constraints are the…
Q: The below data is in first normal form. Normalise the data to third normal form, howing all steps…
A: Here the primary key is {PlayerName, CharacterName} The FDs possible are PlayerName->Player…
i need its answer, all information in image
Step by step
Solved in 2 steps with 1 images
- The Car Maintenance team wants to learn how many times each car is used in every month and day to organize their maintenance schedules. The team wants a table with the following column names and information: Car ID Month Day Count You need to create a summary table using the WITH ROLLUP modifier and grouped by the specific column names, listed above, and send the data back to the team. The table and the output is attachedConsidering the following ERD, which tables and fields (i.e., columns) you don't need to access in order to determine which book titles have been purchased by a customer and when the order shipped? CUSTOMERS Customers Lastname Firstname Email Address City State Zip Referred Region ORDERS Order Customer Orderdate Shipdate Shipstreet Shipcity Shipstate Shipzip Shipcost ORDERITEMS Order Items ISBN Quantity Paideach a) Books: ISBN, Title BOOKS ISON Title Pubda PublD Cost Retall Discount Category Minretall Maxretall PUBLISHER PublD Name PROMOTION Contact Phone b) Orderltem: Order#, ISBN BOOKAUTHOR AUTHOR AuthorD Lname Fname ISEN AuthorlD d) None of the above is true Oc) Orders: Order#, Customer#, Shipdate answer with proper explanation, explanation all options why correct ans why remain incorrectGiven the following relation that stores details of student registration: Stu_ID Name Town Province Course RegisteredDate S101 Malak Al Nadabi Al Khuwair Western ITDR2106 02-02-2020 S101 Malak Al Nadabi Al Khuwair Western ITDR1105 14-09-2019 S102 Nadar Al Hashli Daesit Southern ITDR1102 05-02-2020 S102 Nadar Al Hashli Daesit Southern ITDR1101 05-02-2020 S103 Basit Barkat Ruwi Central ITDR1105 06-02-2020 S104 Yeshan Al Khuwair Western ITDR2106 15-09-2019 If the functional dependencies are consistent with the above relation, put TRUE. If they are not, put FALSE. Stu_ID à Name, Town Town à Province Course à RegisteredDate RegisteredDate à Course Stu_ID, Course à RegisteredDate Stu_ID à Course Stu_ID
- 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…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 SPOT_LENGTH 55 50 20 0 SPOT_PULLTHRU 1 0 0 0 SPOT_ELECTRIC_AMPS 50 50 20 0…PRACTICE ACTIVITY FOR JOINS (using APEX) Table name: ADVISORS ADVISORID ADVISORNAME A102 Sami A103 Alice A104 Suad Tamil A105 A101 Arun Table name: DEPARTMENTS DEPTID DEPTNAME D102 ELC D101 D103 IT BSD D104 ENG Table name: STUDENTS | STUDENTID GENDER STUDENTNAME DOB ALLOWANCE DEPTID ADVISORID STUDENTLEVEL AGE S103 6/1/1999 Male 90 D102 A102 Level 1 Hisham 22 S102 Sara 12/30/1994 Female 45 D103 A104 Diploma 27 6/1/1999 Male 5/6/1995 Male S101 Salim 45 D103 A104 Diploma 22 S107 Khalil 45 D101 A101 BTech 26 S109 Said 10/15/1992 Male 45 D104 A101 Advanced Diploma 29 S100 Ahmad 5/1/1995 Male 45 D101 A103 BTech 26 4/5/1998 Male 2/23/1992 Female S104 Faisal 90 D104 A101 Advanced Diploma 23 S105 Fatima 90 D102 A102 Level 2 29 S106 Arwa 9/12/1999 Female 45 D102 A102 Level 3 22 S108 Ibtisam 8/13/1991 Female 90 0103 A104 Diploma 30 Write the SQL statement for the following: 1. Using the ON clause, display the advisor name and student name for all students in the BTech level 2. Display the student…
- Closely examine the following tables and use it to answer the questions that follow: department course student_type student_category Student registration registration_type Suppose 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 columns in the student_type and student_category tables respectively. - the coursecode and stud_id columns in the registration table are foreign keys in the course and 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 a location text with Ave 3 as part of it. [hint: use the LIKE clause with “_” or “%”] 4. What would…Refer to the dimension tables and EmployeeFact table below. Job Date Location EmployeeFact •JobID •DatelD •LocationID • ( OEmployeelD Title FullDate StreetAddress oJobID JobCode DayNumber City PostalCode OLocationID LevelCode MonthNumber oHireDatelD OBirthDatelD ) SalaryAmount BonusAmount MonthName State Year CountryCode EducationYears PerformanceRating The following query reports the employee with the highest salary amount per postal code for employees with level code 8. Complete the missing values. SELECT CountryCode, EmployeeID, MAX(_ _(A)_ FROM (B). Location, Job WHERE EmployeeFact.LocationID = Location.LocationID AND EmployeeFact.JobID = (C). AND = 8 GROUP (A) Ex: Identifier (B) (C)The marketing team wants to celebrate the success of StayWell with a party. The team wants a table with the names of all residents and owners combined into single column named PARTICIPANT. You need to combine this information from the tables and send it back to the team. You do not need to create a new table in the database schema. Task Return a list of all the residents and owners
- Consider the following PRODUCT table (Primary key underlined). True or False: The two last rows are allowed in the table PRODUCT PRODUCT ID PRODUCT_TYPE_CODE PRODUCT_PRICE PRODUC_NAME PRODUCT_DETAILS Some Description P1 РС-1 23.45 Introduction to Access Some Description Some Description Some Description P2 PC-2 99.25 Plates P3 PC-3 9.99 Forks P4 PC-4 1200.45 Horizon 2000 Tread Mill P5 PC-4 900.00 Soul F80 Some Description True FalseQUESTION 2 Hillar Furniture Enterprise is a furniture store owned by Dr. Hillar Addo of AIT. The materials manager of this company maintains a list of suppliers for each of the materials items purchased by the company from outside vendors. The following table shows the essential data required for this application. Hillar Furniture Company Purchasing Data is shown below: Attribute Name Material_ID Material_ Name Unit_of_Measure Standard_Cost Vendor_ID Vendor_name Unit_price Terms_Code Terms Sample Value 3792 Hinges 3” locking Each GHC 5.00 V300 Apex Hardware GHC 4.75 1 COD a) Using the dependency diagram, identify the functional dependencies for this data. You may assume the following: Each material has one or more suppliers. Each supplier may have supply one or more items or may not supply many items. The unit price for a material may vary from one vendor to another The terms of a code for a supplier uniquely identifies the terms of a sale (e.g. code 2…Create the following tables in your database with the following schema: Table: Employee | Column Name | Type | empId I name | supervisor | salary | int | varchar | | int | int empId is the primary key column for this table. Each row of this table indicates the name and the ID of an employee in addition to their salary and the id of their manager. Table: Bonus | Column Name | Type | | empId | bonus | int | int empId is the primary key column for this table. empId is a foreign key to empId from the Employee table. Each row of this table contains the id of an employee and their respective bonus. Add the following data to your tables: Input: Employee table: | empId | name | supervisor | salary | | 3 | Brad | null | 4000 | 1000 | 2000 | 4000 | John | 3 | 2 | 4 | Dan | 3 | Thomas | 3