Categories
MySQL

B3.

The lab A is scheduled to take 60 to 90 minutes. All work to be completed during the class time. The submission is a text file with your answers.
You are allow to use your books and notes for this lab.
A data set is available for you named Lab3Fall-sql.txt
You need to input this file in Oracle live SQL (all students should have registered to obtain a free student account).
The total grading of the Lab is 2.5 points.
PART A – Working with a Data Set in Oracle SQL Live (Guided)
Run the data set in Oracle live SQL.
A error might occur on the drop statements at the start of the execution but ignore that.
Reason: The first lines of the file (DROP xxx) are put there to in case you run the program more than once make sure that all tables are visible and contain data.
Once you have the data set up and running you will find a number of tables.
PART B – Answer the following questions (in a text/word file to be uploaded in Canvas)
B1. Briefly describe each of those tables giving its name, attributes and purpose
B2. Using the data available answer the following question: Which customers bought a natural ash computer desk?
In order to get this info you will need to:
2.1 Proceed to join the tables before extracting any useful information . Join the tables by giving them the following names :
CUSTOMER_T CUS
ORDER_T ORD
ORDERLINE_T ORL
Look for the matching criteria and join them using those criteria.
Provide a copy of the SQL query you have used for this purpose as the answer for this section.
2.2. Now we have effectively a single table of all the data. State the SQL query related to select customers that bought natural ash computer desks.
( Hint: PRODUCTID of natural ash computer desk is 3)
2.3 Finally, provide the results of the query in 2.2 as your answer for this section plus a pic of the table.
B3. Using joints and conditionals
3.1 Do a query in the same tables of section 2 to find all products made in cherry and all products made in walnut.
2. Submit your query and table (pic) in one file
Once you finish do not forget to upload your answers to Canvas.

Categories
MySQL

A data set is available for you named lab3fall-sql.txt

Note to all students
The lab A is scheduled to take 60 to 90 minutes. All work to be completed during the class time. The submission is a text file with your answers.
You are allow to use your books and notes for this lab.
A data set is available for you named Lab3Fall-sql.txt
You need to input this file in Oracle live SQL (all students should have registered to obtain a free student account).
The total grading of the Lab is 2.5 points.
PART A – Working with a Data Set in Oracle SQL Live (Guided)
Run the data set in Oracle live SQL.
A error might occur on the drop statements at the start of the execution but ignore that.
Reason: The first lines of the file (DROP xxx) are put there to in case you run the program more than once make sure that all tables are visible and contain data.
Once you have the data set up and running you will find a number of tables.
PART B – Answer the following questions (in a text/word file to be uploaded in Canvas)
B1. Briefly describe each of those tables giving its name, attributes and purpose
B2. Using the data available answer the following question: Which customers bought a natural ash computer desk?
In order to get this info you will need to:
2.1 Proceed to join the tables before extracting any useful information . Join the tables by giving them the following names :
CUSTOMER_T CUS
ORDER_T ORD
ORDERLINE_T ORL
Look for the matching criteria and join them using those criteria.
Provide a copy of the SQL query you have used for this purpose as the answer for this section.
2.2. Now we have effectively a single table of all the data. State the SQL query related to select customers that bought natural ash computer desks.
( Hint: PRODUCTID of natural ash computer desk is 3)
2.3 Finally, provide the results of the query in 2.2 as your answer for this section plus a pic of the table.
B3. Using joints and conditionals
3.1 Do a query in the same tables of section 2 to find all products made in cherry and all products made in walnut.
2. Submit your query and table (pic) in one file
Once you finish do not forget to upload your answers to Canvas.

Categories
MySQL

Make sure to commit any changes that you make.

The president’s table has several errors that you need to fix. Make sure to commit any changes that you
make.

Categories
MySQL

Just in case you are having issues with sqldeveloper, you can always connect to oracle cloud.

Instructions
You cannot go back to a previous question, only one question will shown at a time.
Make sure to use the Chrome browser
You will have 90 minutes to complete the test. There is one essay questions (40 points) and 30 multiple choice questions worth 2 points each.
The essay question will appear first. You will be asked to to create some tables and insert some data. This will have to be done in Oracle. You will have to provide printscreens of your SQL statement along with the oracle message for the given SQL statements.
Just in case you are having issues with SQLDeveloper, you can always connect to Oracle Cloud. Make sure that you have already created an account for yourself before the test.

Categories
MySQL

The erd should include all the entities, relationships, and connectivity where applicable.

1. Write a list of business rules ( not less than 4).
2. Create a relational diagram for the database using crow’s foot notation. The ERD should include all the entities, relationships, and connectivity where applicable. Additionally, please specify the business rules underlying your ERD. You can use the online drawing tools I mentioned in the syllabus or any other tools you prefer. After you draw the ERD, export/screenshot it into a jpg or png and insert the picture into a word document. Then rename the document to “Yourlastname_Assign2_Q3.docx” (for example, mine will be: “Zhang_Assign2_Q3.docx”).
Draw the ERD in Crow’s Foot model of the database presented below. The ERD should include all the entities, relationships, and connectivities.
3. Write a list of business rules ( not less than 3 )
4. Create a relational diagram for the database using crow’s foot notation. The ERD should include all the entities, relationships, and connectivity where applicable. Additionally, please specify the business rules underlying your ERD. You can use the online drawing tools I mentioned in the syllabus or any other tools you prefer. After you draw the ERD, export/screenshot it into a jpg or png and insert the picture into a word document. Then rename the document to “Yourlastname_Assign2_Q6.docx” (for example, mine will be: “Zhang_Assign2_Q6.docx”).

Categories
MySQL

Here is the assignment

Learning Goal: I’m working on a mysql question and need guidance to help me learn.
here is the assignment

Categories
MySQL

Here is the assignment for this :

Learning Goal: I’m working on a mysql question and need guidance to help me learn.
here is the assignment for this :

Categories
MySQL

Learning goal: i’m working on a mysql question and need guidance to help me learn.

Learning Goal: I’m working on a mysql question and need guidance to help me learn.
Finished this word document all of this questions:

Categories
MySQL

Spotify

Learning Goal: I’m working on a mysql question and need an explanation and answer to help me learn.
4.The Word document should contain the requested screenshots and
answers to any questions
5. Each screenshot image must include a caption (e.g., Screenshot1:
ERD1 Shipment)
6. Your screenshots should be legible
7. A professional quality work is expected. Points will be deducted for
unprofessional work.

Environment
• VM BCIS 4660: SQL Server
• ERDPlus https://erdplus.com/standalone

Exercise: ERD & Database Design
This assignment is designed to begin familiarizing you with database conceptual design using Entity
Relationship Diagrams.
1. Observe the ER diagram for the GreenShop and answer the following questions about the
diagram (5pts)

Page | 2

a) How many tables will the relational schema mapped from the GreenShop ER diagram have?
a. ________

b) What is the primary key column in the Promotion table?
a. ________

c) How many columns will the following entities have in the relational schema mapped
from the GreenShop ER diagram?
a. Promotion ____
b. Product _____
c. Company _______

2. In GreenShop, each employee reports to one department and a department must have at
least one employee reporting to it. However, each department but could have many
employees reporting to it. In the ER Diagram below, what symbols should represent the
relationships (e.g., Zero or more, One and only one, One or more). Please justify the
cardinality assignment. (5pts)
a. Symbol 1 should be ___
b. Symbol 2 should be ___

Page | 3

3. In the ER Diagram for the BCIS4660 Course, what symbols should represent the
relationships (e.g., Zero or more, One and only one, One or more). Please justify the
cardinality assignment. (5pts)
a. Symbol 1 should be ___
b. Symbol 2 should be ___
c. Symbol 3 should be___
d. Symbol 4 should be___

4. In the ER Diagram for the Music, what symbols should represent the relationships (e.g., Zero or more, One and only one, One or more). Please justify the cardinality assignment.
(5pts)
c. Symbol 1 should be ___
d. Symbol 2 should be ___
e. Symbol 3 should be___
f. Symbol 4 should be___

5. In this exercise, please put your learned concepts into action by converting the following scenario
into an ERD.
A manager wants to store information on their employees. The employees are identified by an
Employee ID. In additional to storing the employees’ last name, first name, phone number, and
zipcode, each employee is assigned to a department which is identified by the department ID. Each
department has a budget amount, and department name.
Please create an ERD that captures this information about this organization. Be certain to indicate
identifiers and cardinality. Please justify the cardinality assignment. (Screenshot1)
Grading (5pts). Make sure that in the ERD, the:
• Entities are correctly identified:
• Attributes are correctly identified:
Page | 4
• Primary keys are correctly identified:
• Relationships and cardinality are correctly identified:

6. In two sentences, please describe the relationships in the image below. Make sure to
describe the two directions in the image. (5pts).

7. In two sentences, please describe the relationships in the image below. Make sure to
describe the two directions in the image. (5pts).

8. You have just started working as a Junior Data Analyst at GreenShop. Your manager hands you
an Excel spreadsheet with the following columns (see table below) and asks you to design a
database to store this data (10pts).
a. Describe the steps to normalize this dataset (table) up to 2NF?

b. After this dataset is normalized, how many entities should this dataset have? (Hint: start
by identifying the different entities present in this dataset)

c. Use ERDPlus to design the ERD for entities and attributes. (Screenshot3 – make sure to
show the 4 required elements in your screenshot)

d. Write and execute the T-SQL to create the tables. Please use elegant and rerunnable scripts
Meaning, there should not be any errors in your script. (HINT: USE “databasename”;
DROP “object type” IF EXISTS). (Screenshot4 – make sure to show the 4 required
elements in your screenshot, including the expanded objects in the object explorer).

e. Using the SQL Server database diagramming tool, create and view the E-R-Model of the
physical implementation.
Page | 5
i. Provide a short description of your actions
ii. Provide a Screenshot5 showing E-R model. (See instructions)

EnrollID StudentID FirstName LastName EnrollDate CourseID CourseName1 CourseID CourseName2
1011 91231 Jay Smith 2022/02/20 1123 BCIS4660 1156 BCIS 4570
1012 89091 Leslie Ogundele 2022/02/20 1124 BCIS 4630 1123 BCIS4660
1013 23347 Izee Ram 2022/02/20 1156 BCIS 4570 1123 BCIS4660
1014 84354 Lola Lin 2022/02/20 1124 BCIS 4630 1123 BCIS4660
1015 34536 Pau Lilipot 2022/02/20 1124 BCIS 4630 1123 BCIS4660
1016 45456 John Carrie 2022/02/20 1156 BCIS 4570 1123 BCIS4660
1017 33435 Mark Luad 2022/02/20 1124 BCIS 4630 1123 BCIS4660
1018 35345 Red Darlion 2022/02/20 1156 BCIS 4570 1123 BCIS4660
1019 35378 Sri Rino 2022/02/20 1124 BCIS 4630 1123 BCIS4660
1020 98065 kalyani Cruz 2022/02/20 1156 BCIS 4570 1123 BCIS4660

9. Database reverse engineering: (5pts)
a. Pick one of the following websites and perform reverse engineering to identify its major
entities and their attributes (4 or more entities). List the entities and their attributes.
i. Entity1 (attrib1, attrib2, attrib3…)
ii. Entity2 (attrib1, attrib2, attrib3…)
iii. Entity3 (attrib1, attrib2, attrib3…)
iv. Entity4 (attrib1, attrib2, attrib3…)
v. ……

b. Use ERDPlus to draw an ERD that represents the entities and attributes, as well as the
relationships between the entities. (Screenshot)

c. Describe the entities and attributes and what you accomplished.

American Airlines (aa.com)
Travelocity
Twitter
LinkedIm
Canva
Tiktok
Match.com
Canvas
Spotify

Categories
MySQL

Easy 10 sql query questions

Learning Goal: I’m working on a mysql question and need guidance to help me learn.
easy 10 sql query questions