Databases Questions and Answers August 14, 2021

Explain how the foreign key constraint maintains referential integrity between two tables when the database is inserted, updated, or deleted. What are the different 'referential actions' (foreign key rules) that can be specified?

What is foreign key constraint in databases Briefly explain by using an example. What is the difference among a base table, a virtual view and a materialized view

Using SQL statements.

Given the following table descriptions, write a sql statement that will generate a foreign key constraint on the sales bid attribute that references the books bid attribute. MariaDB ugc1201 desc books Field Type Null Key Default Extra int(10) YES 1 bid title varchar (10) YES author varchar(15) YES subject varchar(10) YES 4 rows in

You have been asked to fix some inconsistencies in a database. After running the following query, select productid , price from order_item; you notice that there are product id's listed in your result set that do not match the products in your database. As this provides an inaccurate report, how do you fix this?

What is the foreign key constraint (5 marks) Please give an example to explain set difference of two relations. (5 marks)

Identify any key or foreign key constraint violations in the following instances of Table2 and Table3.,1. We are given the following two table schemas Table2(E, F, G, H, I, J) Table3(K, L, M) We are also given that there is a foreign key reference from L, M of Table3 to (G, H of Table2. Identify any key or foreign key constraint violations in

How I can insert values in the Orders table and fix this error? error: mysql> insert into Orders VALUES (1204305, 100004, 'ADV-24-10C', 'Advanced Switch 10GigE Copper 24 port'); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`QuantigrationRMA`.`Orders`, CONSTRAINT `Orders_ibfk_1` FOREIGN KEY (`CustomerID`)

Error code 1215 Cannot add foreign key constraint in MySQL Workbench CREATE TABLE QUALIFIED (FacultyID INT NOT NULL, CourseID CHAR(8) NOT NULL, DateQualified DATE, CONSTRAINT Qualified_PK PRIMARY KEY (FacultyID, CourseID), CONSTRAINT Qualified_FK1 FOREIGN KEY (FacultyID) REFERENCES Faculty (FacultyID), CONSTRAINT Qualified_FK2 FOREIGN KEY

Apply the following constraints on the Course Table primary Key . not null . check courseno CPE210 CPE122 CPE220 Cname DBMS Teacherno courseno OOP Microprocessors Crhr 3 Coursename days 13 crbr 24 13 B Apply the foreign key constraints on the following two teacher and course tables given below (Note insert data in the two tables) Teachername time

Add a foreign key constraint on column vend_id of table Products that refers to table Vendors's primary key, can we add the two new products?

Create a child table empla with columns empo, depeno, and design with appropriate datatype Create a column level foreign key constraint with column depeno that has reference to column deptno of the parent table Dept. The parent table is Dept which is already created. Table Level Foreign Key Constraints First we need to create a parent table Then

Create the database tables per the relational schema provided. What does this foreign key constraint status mean? Please provide your answer as a "comment" to your sql code and tag your answer with 1.1.1 (similar to the way I explain each line of code). Which foreign key constraint status do you prefer in your database: RESTRICT DELETE (Default

SALES TRANSACTION STransactionID Qty Sales SysDate Time SalesRepID (FK) SALES REP SalesRepID RepName TerritoryID (FK) TERRITORY TerritoryID TerritoryName

Create a table for Section entity as identified in Q2 (a) using SQL statement. Note Primary key and foreign key constraints should be indicated clearly in the statement,CourselD Teaches ProfNum Term 1 Professor M ProfName CourseName COURSE has 1 E M Section Offsite Section Location M Section Num Enrol M Student StudentNum StudentName Mark GPA

Add referential integrity between: a. Department table and Employee table, add foreign key constraint in the Employee table. b. Employee table and Dependent table, add foreign key constraint in the Dependent table c. Department table and Project table, add foreign key constraint in the Project table . d. Employee table and EmployeeProject table,

Can you explain why foreign key constraints are important to me, a bit confused. Also what should I do if databases do not support foreign key constraints?

Apply foreign key constraint between the two tables, teacher and course you do not need to full the tables, the important is the key Teacherno Teachername officeno courseno coursename crhr day Teacherno

How to create foreign key constraints for a tables in a database from the CLI in MySQL through the ALTER TABLE command?

write the SQL statements to do the following: a. Modify the CUSTOMER table so the customer number field is set to auto increment and the auto increment value is set to 2000. (Note: The INVOICE table will need to be modified first to remove the foreign key constraint on the customer number. After the auto increment value property has been added to

1. DROP SCHEMA IF EXISTS ch08simpleco 2. CREATE DATABASE ch08simpleco 3. USE ch08simpleco 4 5 6 7 8 9 10 11 12 13 14 125 9 16 17 18 CREATE TABLE customer ( ) CUSTNUM INT (11) PRIMARY KEY, CUSTLNAME VARCHAR(30), CUSTFNAME VARCHAR(30), CUSTBALANCE DECIMAL (8, 2) CREATE TABLE invoice ( ) INVNUM INT (11) PRIMARY KEY, CUSTNUM INT (11), INVDATE DATE,

What must exist on the Parent table before Oracle will allow you to create a FOREIGN KEY constraint from a Child table

the following situation you need to DEFINE (produce SOR), DESIGN (Produce ERD and LDBD (RDM)), DEVELOP (DDL statements, DML data statements, and the DML information statements), DEPLOY (execute the above statements in either MS ACCESS or SQL Server), and OPERATE (run the data the Data Base System (DBS) to create the metadata, populate the data,

ERROR 3780 (HY000) at line 14: Referencing column 'HorseID' and referenced column 'ID' in foreign key constraint 'lessonschedule_ibfk_1' are incompatible. Correct this code:

1 CREATE TABLE Horse ( ID 2 3 RegisteredName VARCHAR(15), 4 PRIMARY KEY (ID) 5 6 7 8 9 10 11 PRIMARY KEY (ID) 12 ) 13 ) SMALLINT UNSIGNED AUTOINCREMENT, CREATE TABLE Student ( ID FirstName LastName Main.sql SMALLINT UNSIGNED AUTOINCREMENT, VARCHAR (20), VARCHAR(30), 14 CREATE TABLE LessonSchedule ( 15 HorseID INTEGER NOT NULL, 16 Student ID

In July 16, 1945, the first explosion of an atomic bomb (Trinity test) occurred in the desert in New Mexico, unleashing the tremendous forces of nuclear energy to humanity and forever changing the world. The energy released by this first fission bomb was classified information. In 1950, images of the explosion were released and were published by

Transform the following ERD into a set of relations in 3NF; list all foreign key constraints

1. Transform the following ERD into a set of relations in 3NF list all foreign key constraints. (40 points) Specialty PK Specio Description Physician PK Physio PhysName() Pacient PK Patientib FirstName LastName Phone Hospital OH PK HosplD Name Address () PK Treatment Treat Code Description Price UrgentCare TimeOpen TimeClose ER NoOfBeds

Remove the entire column prod_id from the movie table: First, drop the foreign key constraint from the movie table. You can use the command SHOW CREATE TABLE movie; to see the name of the foreign key constraint you need to drop. Next, drop the column prod_id from the movie table 2.List the movie titles and the names of their production companies.

mysql select from movieprod movieid prodid 2 11 4 6803 WOWNTOFIN id name 16 HH to 12 13 5 3 7 9 10 14 17 mysql DESC MOVIE Field int 1 1 2 2 3 3 4 5 5 5 5 5 5 19 18 18 rows in set (0.00 sec) 5 6 SO7 6 id title year duration gross rating prodid int 7 rows in set (0.17 sec) mysql DESC PRODUCTION Field Type varchar(80) int int decimal

Apps Appid 1 2 3 4 5 6 7 Developer Developerid 112 109 100 106 Appname Facebook Twitter iwork Snapchat Instapaper Instagram finder rating 4.1 4 2.2 3.9 3.1 4.4 3 Developername The coders Rabih Haidar Fb developers Snap developers country china Lebanon USA USA category Social media Social media productivity Social media news Social media

command coming out wrong

mysql mysql mysql alter table songs add foreign key(artistid) references artist(id) ERROR 1452 (23000) Cannot add or update a child row a foreign key constraint fails (music. sql5b10, COM EFERENCESartist (id)) mysql

DEVELOP THIS CHART, TO HIHGLIGHT THE MAX VALUE FOR EACH QUESTION, WHAT CAN BE THE CODE FOR THIS IN TABLEAU?

Survey results Question 1 Poor Excel Average Good Very Good Question 2 Very Poor Tableau Poor Average Good Very Good Question 3 No knowledge Photoshop Very Poor Poor Average Good Very Good Question 4 Average PowerPoint Good Very Good Question 5 No knowledge Google Very Poor Charts Poor Average Good Question 6 No knowledge JavaScript Very Poor Poor

Can you think of other data points that the organization might collect that they did not mention? What other skill sets do you think are needed for employees to be more data-driven? How do you think data governance can positively impact the decision? What could be a plan for capturing and using data in the organization? Are there any concerns

Insert the following data in the tables usinginsert into statements: customer: 10010, Johnson, Albert, A, 612, 8442573 10011, Edwards, Leona, K, 763, 8941238 10012, Smith, Walter, W, 612, 8942285 10013, Roberts, Paul, F, 612, 2221672 10014, Orlando, Myla, NULL, 615, 2971228 invoice: 1001, 10011, 2008-08-03 1002, 10014, 2008-08-04 1003, 10012,

SELECT query, what is the difference between a WHERE clause and a HAVING clause? Give a complete SQL Oracle code example of each that is different from what is in the book and explain each showing result with data. 2. What is the difference between the COUNT aggregate function and the SUM aggregate function? Explain using complete SQL

List all fields that are totally dependent on the PK fields (empid and langid) 2. List all fields that have partial dependence (on one or the other PK fields), and tell which of the PK fields it is dependent on 3. List all fields that have transitive dependence, and tell which field it is dependent on,At AI Global, we keep track of employee

Using a single SQL query , I want you to provide me with the following set of summary information from the Instructors table in the MurachCollege database. The first column should provide the last name of the instructor (column titled Last Name). The second column should provide their monthly salary calculated from their annual salary (column

create a new database called Assignment1firstnamelastname replace firstnamelastname with your first name and last name. 2. After you have created your database successfully then switch to it using the USE keyword. 3. Create a Table called Dealership, it will look like the below table. The datatype will be in the second row. Any constrictions on

I try to run it, I receive ORA-00909: invalid number of arguments. Where did I go wrong? Display the full names of employees (including title and initial) who manage other employees, ordered by last name in descending order. Don't forget to include the title, initial, first name, and last name and look at the data given to determine how to

Provide an analysis of sales performance by Promotion. It would be interesting to see how different types of promotions drive sales (quantity and revenue), especially by product category or region. The comparison between Internet and Reseller sales is probably interesting as well. (Hint: don't attempt to do everything, but show some good analysis

Make a Conceptual Data Model (using Draw.io or Visio), containing entities and their relationships as they exist in the problem domain (including any M:M relationships) 2. Please make a Physical (Logical) Data Model using MySQL Workbench based on the conceptual model. Project Synopsis: Your team has been hired by ABC Bus Transit Company to create

Show the total sales (overall) by year rolled up by the Territory group and country. A special request from management is that the United Kingdom is no longer part of the European Union (EU) and they would like to see the UKs totals as a separate Territory group. You cannot modify the data, so you will need to address this request in your query.

Draw Use case diagram and sequence diagram from the given EERD

Date of published articles Publisher Catgeory 1 Rating Publishes N Name Viewer rating UID Writes Rates User Storage N CID ....... Replies N Email Not registered user Saves N Comment N saved ID Provinces N N N 1 Politics Has Gaming Manages Type reads N Article N Has N Category 200 TO N World News Country N Manages Date Title N Type Sports DO

Draw an ER diagram to capture the following information in a database for the airline company. An airport is identified by a unique airport code and has a location which consists of (city, state), an elevation, and one or more phone numbers. An airport may have several flights to arrive at it and depart from it. A flight has a unique flight

Issue Wages Current average wage at MMM 10.35hour Average wage of local nonunionized workers in this industry 10.25hour Average wage of local unionized workers in this industry 10.55hour Average wage of unionized workers nationally in this industry 10.95hour Inflation has increased 5 over the past year Every .05hour increase in wages will cost the

how do i implement the to other if statements in this function? ok so i have an admin user called ' USERX ' that contains data now im trying to give privileges to three other users. so i am creating a function policy with roles USERY can only read 'A' USERZ can only read 'M' USERZZ can only read 'Z' So I've managed to do the policies and all the

READ A FUNCTION, Zachary Jilesen 041017349 CREATE OR REPLACE FUNCTION MYFUNC (pschema varchar2, Pobj varchar2) return varchar2 as dpredicate VARCHAR2 (2000) myUser VARCHAR2 (2000) roleFound int BEGIN dpredicate 1 1 myUser SYSCONTEXT (USERENV, SESSIONUSER) SELECT COUNT() AS Found into role Found FROM SYS.DBAROLEPRIVS WHERE GRANTEEUPPER (myUser)

Make a database and execute the code below in SQL Server's query window to make the database tables. CREATE TABLE PhysicianSpecialties (SpecialtyID integer, SpecialtyName varchar(50), CONSTRAINT PK_PhysicianSpecialties PRIMARY KEY(SpecialtyID)) go CREATE TABLE ZipCodes (ZipCode varchar(10), City varchar(50), State varchar(2), CONSTRAINT

Object Explorer SQLQuery1.sql ME.... Melesalmbolt (52)) x select from Patients 100 Results Messages PatientID FirstName Middlelnitial LastName AddressLine1 Address Line2 ZipCode Phone Home Phone Alternate Email 2973 Lincoln Street Apt 15A Greater 2074 Owen Lake Road NULL 24562 (434) 2431357 NULL Nathan 976 Stroop Hill Road NULL 24561 (540)

Write queries to answer the following questions. Your instructor may specify the method of acquiring the database noted above and whether to use Design view or SQL view. a. For which items are there at least 100 units in QuantityOnHand? b. What were the item numbers, price, and quantity of each item sold on invoice number 103? c. Which customers

Table 417 Problem 4.5 Tables Customer CustomerID CustomerName ........ Inventory 1000 Smith 1001 Jones 1002 Jeffries 1003 Gilkey 1004 Lankford 1005 Zeile Sales 00000 1006 Pagnozzi 1007 Arocha ItemID. City Description StdCost 1010 Blender 1015 Toaster 1020 Mixer 1025 Television 1030 Freezer 1035 Refrigerator 1040 Radio 1045 Clock Invoice. SaleDate

Create ERD based on the following tables. The underlines attributes are primary keys. The links are connection between primary keys and foreign keys EMPLOYEE FNAME MINIT LNAME SSN BDATE ADDRESS SEX SALARY SUPERSSN DEPARTMENT ESSN DNAME PROJECT PNAME DEPT LOCATIONS DNUMBER DEPENDENT DNUMBER MGRSSN PNUMBER ESSN DLOCATION WORKS ON PNO DEPENDENT NAME

define the database structure for a Movie database. Your structure should incorporate the discussions from class, and implement the principles of data normalization (up to 3rd normal form) as described in Chapter 10 and in class. Your database must track data for the following movie related information movies actors, directors, producers

Display the names of the authors in Authors table. The first name and last names must be combined. (1 points) List titles of all books alphabetically in Books table along with their price and discount. (

Specify how a database management system (DBMS) could be used to preclude the problem. In the file system shown below, one business rule is that each project has one manager. Identify two (and no more than two) problems with the data structure or inconsistencies with the data contents. Indicate why the items you posted are an issue.

ProjectCode ProjMgrFirst ProjMgrLast ProjectMember ManagerPhone MemberPhone A. B. Smith Louis White Tom Fitts Joan Jen M. L. Jones L. Patel Annie Smith R. Patel Marylou Jones Ram Reddy 5129023897 3028732310 5129023897 3129839080 5129023897 9035732098 5129023879 9999999999 3025719856 6135349082 3025719856 6143482098 3025719856 3028732310 3025719856

A new room booking system is required to help guests reserve a room based on the following requirements Single, Double or Suite. Room Price (The maximum price the user could afford). With or Without Balcony. With or Without Lounge. . . When a suitable match is found, the guests (identified by their eMail) should be able to reserve the room. Room

Design a store database Your store should sell one type of thing, like clothing or bikes, whatever you want your store to specialize in. You should have a table for all the items in your store and at least 5 columns for the data you think you'd need to store. You should sell at least 15 items, use select statements to order your items by price,

Create ERD for each story. Add Attributes to each Entity. Mark Identifier Attribute with # ( Example: #EmployeeName) 1. Company, Department, Employee 2. Person, Pet 3. Student, Sport Club Use Lucid tool to create an ERD, download in PDF, submit.

List the item ID, description, and price for all items. Task 2: List all rows and columns for the complete INVOICES table. Task 3: List the first name, last name, and credit limit of customers with credit limits of $1,000 or more. Task 4: List the invoice number for each order placed by customer number 125 on 2021-11-15. Task 5: List the customer

CUSTID FIRSTNAME LASTNAME ADDRESS 125 182 227 294 314 375 435 492 543 616 721 795 Joey Billy Sandra Samantha Tom Melanie James Elmen Angie Sally Leslie Randy Smith Rufton Pincher Smith Rascal Jackson Gonzalez Jackson Hendricks Cruz Smith Blacksmith 17 Fourth St 21 Simple Cir 53 Verde Ln 14 Rock Ln 1 Rascal Farm Rd 42 Blackwater Way 16 Rockway Rd

Explain briefly with an example how an update anomaly might occur. b) How can you avoid the anomaly Student number Student name 1198121 Nosheen 2729923 Sachin 19828313 John 8837383 8792892 57892829 9828980 7729922 9229923 b) Nosheen Emon Your answers Fahimeh Robert Daniel Maria COURSE CLASS Grade Teacher ID Teacher name Teacher phone 7 2982029

Write out the steps of your chosen problem-solving approach, and apply them to your problem one by one. Problem Identification: I am feeling overwhelmed and anxious due to the workload of assignments and feel like I'm not planning my time wisely due to waiting until the last minute. I have to make sure my assignments are completed by the

Write the SQL code (must be TYPED out please , I cannot read script or bad penmanship). Mainly using PostgresSQL that will, for each employee, yield the total # of HOURS worked, and the total charges stemming from those hours worked. The results of running this query are shown below: Write the SQL code (must be TYPED out please , I cannot read

29. Using the data in the ASSIGNMENT table wwwwww. AAAAA ASSIGN NUM ASSIGNDATE PROJNUM EMP NUM ASSIGN JOB ASSIGNCHGHR ASSIGN HOURS ASSIGNCHARGE 1001 103 3.5 1002 117 1003 117 1004 103 108 104 113 103 115 117 105 108 115 104 103 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 22Mar04 16 22Mar04 22

Identify all queries that meet the stated specifications. 2. For each query that does not meet the stated specifications briefly state exactly what is wrong with the query. If there is an error briefly explain what the error is. If it does not provide the required results briefly explain why Question1(40 points) Which of the following queries

Diagram a conceptual (ER) data model for the following case. Be sure to indicate your entity names, cardinalities, and primary ids. It is not necessary to label your relationships unless it adds clarity. Account for the attributes shown here but it is not necessary to add additional attributes. Draw a model using pen and paper and then upload it.

MY 7 2NF MV TD MV

Which SQL commands would be used to accomplish these tasks? Find IDs and names of all students taking ART103A. Find the numbers of all the classes taught by Byrne of the Math department. Get IDs of all faculty who are assigned to the History department or who teach in room H221. Find the names and IDs of all faculty members who teach a class

Draw a Conceptual Data model ( You Must attache photo of First draft ERD). Data Requirements The purpose of this assessment item is to identify and fully document the database data requirements for the project. The database must be able to store information about: Products Customers details Registration (creation of customer accounts) Customer

Show the SQL Server code that displays: Display all of the data for the Employees who have orders What would the server code be An organization wishes to keep track of their orders along with the employee who processed the orders. The data they need includes the employee number, their start date, their salary and the commission percentage (the

Given the following tree structure Give one reason why it is not a proper B tree 2 4 16 12 8 25 33 41,Given the following tree structure Give one reason why it is not a proper B tree 2 4 16 12 8 25 33 41,Given the following tree structure Give one reason why it is not a proper B tree 2 4 16 12 8 25 33 41

creating a database to modernize the inventory control and distribution of content. In order to complete this task, you must first establish a complete list of the user requirements (i.e., establish business rules, use cases, build an appropriate data model, etc.). You can rely on competitors such as Netflix, Hulu.com, and Amazon Instant Video for

Please fill out table, answer questions according to the table and for the graph, please screenshot and colour in where the dots are supposed to be as it didnt allow me to input a file

ERSC 1P94 Assignment 2, Crater Counting (MilankovicSchaeberle) In this lab exercise you will determine the age of a planetary surface using the crater counting method. Crater counting uses the frequency of occurrence of given size craters (by diameter) to estimate the age of a surface since it was last reworked. You will assess the quantity of

What is a stateless society 2. How is political status achieved in Papua New Guinea compared to some societies in Africa and the Eastern Pacific 3. The article notes five qualities that an ambitious man must have if he wants to achieve success and rise above the crowd in Papua New Guinea society. One is talent. What are the other four Fill in the

how to make this a query: The shipping company wants you to produce lines on a label: Line 1, Line 2, Line 3 such that it will make the address. The person's name belongs on the first line (assume the middle initial is not required). The second line includes the street address. The third line includes the city, state, and zip code.

Discuss something that someone would find particularly interesting from the readings, or something that was discussed in the readings that that a person would like to learn more about. b) What about if Someone may also want to discuss how the insight from the readings could be applied to his respective professional setting and/or a healthcare

Provide a report that includes screenshots of all the tables. First, show us a picture of your ER diagram. The file format should be Doc. Based on the ER diagram attached, use the SQL Server Management Studio (SSMS) database software to make a database. Make tables with the same columns and rows, and fill them with up to 10 sample rows. The

department name) PK department 0000 gender PK SIN (PK employee id email phone number jobtitle name PK room number employees Departments manages employess prepares Rooms room type performancemetrics FK customer i K room number pricepernight Reservation checks in checks out roomdescription confirms PK service id service type price description check

Relational Algebra

Considering R1 and R2. How many instances are there in R1 x R2 R1 R2 A B al bl a2 b2 a3 b3 C D cl dl c2 d2

CustomerID 89 90 Given the following table write sql query to find customer name and city based on country valueusa 91 CustomerName Alfreds Futterkiste CustomerName White Clover Markets Wilman Kala Berglunds snabbkp Wolski Ana Trujillo Emparedados y helados Antonio Moreno Taqueria Around the Horn ContactName Karl Jablonski Matti Karttunen Zbyszek

Write a report in which you complete the following about the case study attached. Brainstorm several alternatives for resolving the ethical dilemma in the case. Analyze alternatives for resolving the ethical dilemma and come to a conclusion. Analyze and document the possible ramifications for at least three alternatives. Then, choose a course of

CHELSEA, ELEMENTARY SCHOOL COUNSELOR CHEN, ADOPTED FROM CHINA Chelsea and Chen Chelsea, a 34yearold white female, moved to Riverbend City five years ago. After she completed her masters internship in school counseling, she took a job as the school counselor in the Riverbend City elementary school system. She is working with Chen, a nineyearold

create the statements as PSEUDO code for the MySQL in your report

Create a list of 5 specific data validation rules. Clean the data by filtering the data sources into valid and rejected output streams by transforming the data with the data validator. Transform the data into a usable format, and document the data validation results. Document any data validation errors that occur, and make corrections. Run the

Assuming the following pivot table is based on a properly designed star schema. Based on what you see here, what would be the name(s) of the dimension(s) that were used from it Store Boston Danvers Dedham Providence Grand Total Gross Sales 24,769.00 11,581.00 11,506.00 570.00 48,426.00 Number of Sales 71 33 33 3 140 Average Sale 348.86 350.94

Construct a clean and concise ER diagram for the RRS database. List your assumptions and clearly indicate the cardinality mappings. Part 2: SQL Queries Write SQL queries: Input a passenger's last name and first name and retrieve all trains they are booked on. Input the Day and list the passengers travelling on that day with confirmed tickets.

full details of all parts in Rational Algebra and b. Tuple calculus (2 points) Get the total number of projects supplied by supplier S1 using SQL,Supply SH 1 2 3 4 5 casos co Part PH Pl P2 P3 P4 P5 P6 Project J J1 EPRESSE J2 J3 J4 35 J6 J7 SSSSSSS SPJ 1 1 2 2 2 2 2 2 3 4 koco9 Hall 5 5 SNAME SMITH JONES BLAKE CLARK ADAMS PNAME NUT BOLT SCREW SCREW

You need to keep track of information about software and programmers. Each software application program has a unique ID (SID) and each programmer has a unique ID (PID). Draw E-R diagrams for each of the three scenarios below. (Use Pen and Paper to draw an ERD instead of LuchidChart or Visio) Assume that each scenario is separate and stand-alone.

MY 7 2NF MV TD MV

The FDs that may exist among the attributes of the current state of table If yes, then list the FDs. The FDs that do NOT exist among the attributes of the current state of the table. If yes, then list the FDs that violate this constraint.,hold and which do not unless we know the meaning of the relationships among the attributes. All we can say is

How would i go about writing a sql Query: Tourista's CEO is also concerned that cottages in some regions are not very profitable. She wants a list of all region names where none of the cottages were ever booked for a price over 500. (Note: Your list should exclude Regions with no cottages).

Region Region Code INT Region Name VARCHAR(45) Indexes Customer CustomerID INT CustomerLName VARCHAR(45) CustomerFName VARCHAR(45) CustomerMInitial VARCHAR(5) CustomerEmail VARCHAR(45) CustomerPhone VARCHAR(15) Indexes H Cottage CottageID INT Cottage Name VARCHAR(45) CottageNumRooms INT Region Code INT Indexes Booking Booking ID INT BookingDate

full details of all parts in Rational Algebra and Tuple calculus 2. Get the total number of projects supplied by supplier S1 using SQL 3. Consider the two tables T1 and T2 shown below. Show the result of the following operations: T1 JOIN T1.P=T2.A T2 T1 LETF OUTER JOIN T1.P=T2.A T2 T1 RIGHT OUTER JOIN T1.Q=T2.B T2 T1 FULL OUTER JOIN T1.P=T2.A

A al al a2 a3 B bl b2 b2 b3 C cl c2 c2 c4 D d1 d2 d3 d3

Create an E-R model with entities, attributes, identifiers, and relationships to provide data storage that accommodates the description of the company. a. All relationships should be labeled with verb phrases. b. Use UML notation for this work. Please review the UML videos, as well as the lucid chart videos provided. c. Relationship lines should

Write a query that returns the alphabetized first letter of the customers last name and the count of active and inactive customers for only those letters where the count of active customers is greater than 30. Label the columns as follows starts with is the first column and the first letter of the customers lastname. activecount is the second

Write a query that returns the alphabetized first letter of the customers last name and the count of active and inactive customers. Limit the results to only those first letters that occur in the lastname colum of the customer table. Label the columns as follows starts with is the first column and the first letter of the customers lastname.

What is the grain of the data that we will look for? 2. What are the dimensions? What type of dimensions are there? 3. What are the facts? 2 Define conceptual and logical data model to inform design Now that you have identified the dimensions and facts important to your service line manager, you need to begin translating this into a model that

rprod itemid VARCHAR (11) prodid VARCHAR(11) proddesc VARCHAR(1024) famnode VARCHAR(50) famlevel VARCHAR(50) commcd VARCHAR(50) supid VARCHAR(50) Aitor quantit VARCHAR(50) H rbasestat statuscode VARCHAR(5) tdescname VARCHAR(50) statuslabelen VARCHAR (100) Indexes

Need the importance (justification) of each entity for a given ERD. 2. Need a table for each entity in XAMPP for a given ERD and please provide the queries and table figures.

name date id Sewage Network produced Waste Water amount city located in connected to date id Area located in Treatment Plant H cleaned Treated Water name amount lives in works in id works in Department name Employee name id id is a Role name,name date id Sewage Network produced Waste Water amount city located in connected to date id Area located

Explanation for what this code is doing

40 SELECT EnglishCountry RegionName, FORMAT (SUM(OrderQuantity), NO) as Total 41 FORMAT (SUM(OrderQuantitydecAllUnits), P2) as Total 42 CASE 43 44 45 46 47 48 49 50 51 52 FROM dbo. DimReseller as r 53 INNER JOIN dbo.FactResellerSales as s ON s. ResellerKey r. Resellerkey INNER JOIN dbo. DimGeography as g ON g. GeographyKey r. GeographyKey 55

Any help you can offer would be appreciated. Been pretty confident with previous assignments but not on this one. Right now for Part 1 I'm thinking: Cruise Number Cruise Start Date Cruise Number Cruise End Date Cruise Number Cruise Director Cruise Number Ship Number Ship Number Ship Name Ship Number Ship Builder Ship Number Launch Date

Examine the unnormalized table on the last page of this handout. Using Figure 7.27 from the text as a template, identify the functional determinants and their associated dependencies. Explain your rationale. (25 points) Using Figure 7.30 from the text as a template, convert the unnormalized table into First Normal Form (1NF). Explain the

Add references Enterprise Discuss this type of data model. How it differs from other types? Why to use this type of data model? Conceptual: Discuss this type of data model. How it differs from other types? Why to use this type of data model? Logical Discuss this type of data model. How it differs from other types? Why to use

Demonstrate your understanding of SCD Type 1/2 Make some changes to the Excel file and to the SCD Types in the Dimension Lookup step. Try mixing SCD type1 and 2 for various columns to as to learn how it impacts the target dimension table. Create an expected result set manually in Excel and explain the reason briefly. Execute the ETL and compare

What would be the entities , relationships, attributes( are there any complex/ derived attributes) ,,which attributes can be null ?in this database system if you can may you pls provide the ER diagram as well

The system must allow users to capture the clients information. All the clients are identified by a unique number only allocated to them. When registering new clients, they are required to provide names, age, sex, address, and contact details. For security reasons and monitoring, the new bank system asks the clients to update their ID numbers and

Providea report using the given template and your work, including screenshots of all the scripts and their database results. Execute the SQL statements below against the database you created: Select Minimum of three of Delete, Insert, and Update At least three of the Max, Min, Sum, Count, and Ave Alter Table to add a Column

Room room number room type price per night description employee id Employee employee id SIN gender omal phone number job name department id Departments department id 8 FK Payment Rem TK Room Employee department name employees performance metrics FKR Room FK Employ Departments Payment paymentid Reservation reservationid checkin dato customerid room

create both 1) a flowchart and, 2) a VBA program in Excel that will pick the right number of buses and that will calculate the correct price for the number of people and hours specified. Assume you have just been hired by Olympus Tours (OT), a bus-rental company located in Orem, Utah. OT provides buses for groups such as schools, family reunions,

Got a errors that say "ERROR: Column ProdcutID could not be found in the table/view identified with the correlation name T" x3 and then "ERROR: Column ProdcutID could not be found in the table/view identified with the correlation name S" x3 "ERROR: Column OrderDate could not be found in the table/view identified with the correlation name S" x4 and

What error(s) prevent me from running the PL/SQL data?

Language SQL A Rows 10 Clear Command Find Tables 1 create table eurodata as (select from WKSPORAGARRITAT01.eurodata) Select from eurodata 2 Results Explain Describe Saved SQL History Error at line 170 ORA00933 SQL command not properly ended ORA06512 at SYS.WWVDBMSSQLAPEX220200, line 828 ORA06512 at SYS.DBMSSYSSQL, line 1658 ORA06512 at SYS.

If you have not already done so, you will need to make sure you have access to the database management software that is required for this project, Microsoft Access. Review the Syllabus module for information about accessing this software. Step 2: Open your database management software. Save a new database, and give it a title (e.g., "infosys305").

File Edit Relationships ables Home Create External Data earch... X Clear Layout Relationship Report Tools CREDIT CARDACCOUNT CUSTOMER ITEMTYPE MERCHANT SALE La Database Tools Help Relationships Design Hide Table Direct Relationships All Relationships Relationships Add Tables Relationships X CUSTOMER CUSTOMER ID (PK) CName CCity CState CZip CPhone

How do you join tables in SQL? 2.must you qualify names in SQL commands? How do you qualify a column name? 3.List two operators that you can use with subqueries as an alternate way of performing joins. 4.What is a nested subquery? In which order does SQL evaluate nested subqueries? 5.What is an alias? How do you specify an alias in SQL? Why would

Create two tables in the practice database. Step by step The Employee table has the attribute: Emp_ID, Emp_Name, Office_ID, where the Office_ID is used as the foreign key linking to the Office table. The Office table has the attribute: Office_ID, Location. Please set up the data type constraints according to common sense. For example, the Emp_ID

Generate a listing of all purchases made by the customers. (Hint Multiple related tables need to be joined). Only parts of result are shown below. CU8000 IN NUMBER IN DATE 13011 100 m SH 3008 10011 1000 10014 2035 10012 13012 0012 10011 10011 10011 20014 10004 181 201 DESCRIPT ALIEN, S4. THE 20140135010000 20140117000 Came 20140117000000 28

Show the SQL query. b) Run the query in MySQL. How many rows are returned? c) List just the first two rows from the results of the query. (You can "cut and paste" the query results; Otherwise, you can also type them, referring to the results of the query) This query refers to the stocksdb. (In MySQL, make sure you run the command - USE stocksdb;)

Identify entities and attributes of each entity to support database solution of Social Media Industry. Must be at least 7 fully normalized entities (appropriate relationships, primary keys, foreign keys, data types, comments on each attribute, proper naming conventions etc.). THERE MUST NOT BE ANY CLOSE LOOP AND MANY-T0-MANY RELATIONSHIPS.

Create a nonclustered index on any one table. Feel free to use any table from AdventureWorks2019 database. Create a clustered index on any one table. Feel free to use any table from AdventureWorks2019 database. Use a 3 part name for the table (ex. Databasename.scema name.table name) Create a nonclustered index with a unique constraint on 3 columns

The figure 1 below presents the relationship between wage (million VND per month) and education (years of schooling) of 20 workers. 25 Wage (milliom VND per month 20 15 5 Wage 5.21 0.74 Education 5 60 ene 10 Education (Years of Schooling) Figure 1. The relationship between wage and education 15 20 a. Comment on the relationship between wage and

Run Project management database Run 4 tables based on the diagram below Include all of constraints based on the ERD diagram and based on your assumptions Include primary key and foreign key Use identity function for the primary key in the Project table Populate each table with 5-10 records

project Pname varchar(15) Pnumber int(11) Plocation varchar(15) Dnum int(11) department Dname varchar(15) Dnumber int(11) Mgrssn char(9) Mgrstartdate date HI workson Essn char(9) Pno int(11) Hours decimal(3, 1) employee Fname varchar(100) Minit char(1) Lname varchar(100) Ssn char(9) Bdate date Adress varchar(100) Sex char(1) Salary decimal(10,