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

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

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

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

Easy 10 sql query

Learning Goal: I’m working on a mysql question and need an explanation and answer to help me learn.
easy 10 Sql Query

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

Categories
MySQL

2 has been added to the number to remove the comma and space that follows it.

Learning Goal: I’m working on a mysql discussion question and need guidance to help me learn.
Submission: FirstNameLastNameLab1.py file
#Part A:
We will learn how find a substring within a larger string using the index() function.
Open Thonny and start a new file by clicking ‘File’ and selecting ‘New’. Add a comment to the first line with your first, last name, and Lab 1, like this:
#Michael Deamer Lab 1
Save the file as FirstNameLastNameLab1.py, like MichaelDeamerLab1.py. This will be the file you submit for credit.
There are two parts to this Lab. Indicate that this section deals with Part A by adding a comment:
#Part A
Create a variable to store a full name, written last name first, first name last, with a comma separating the two:
fullName = ‘Shaw, Bernard’
Find the index of the comma:
commaIndex = fullName.index(‘,’)
Substring the variable from Step 3 to just the last name. The commaIndex variable from Step 4 will indicate where the string should stop:
lastName = fullName[:commaIndex]
To find the first name, the commaIndex variable will indicate where the string should begin:
firstName = fullName[commaIndex+2:]
Note:
2 has been added to the number to remove the comma and space that follows it.
Concatenate the first name, a space, and the last name to create a modified full name:
modifiedFullName = firstName + ‘ ‘ + lastName
Print the results:
print(modifiedFullName)
Note:
This code can now be used for any name listed last name first by changing the string in step 4.
#Part B
We will build a simple tip calculator that will:
ask a user for the original bill amount;
ask what percent she/he would like to tip;
calculate the total plus tip; and
print the total.
Here are the steps involved:
Add a comment to indicate that this is Part B:
#Part B
Ask the user the original bill amount using the input() function and save this input as a variable:
originalBill = input(‘Please enter the bill amount:’)
Similarly, we can ask the user for the desired tip percent:
tipInput = input(‘Please enter the percent you would like to tip:’)
Let’s add a print statement and run our code to check if it is working correctly:
print(originalBill, tipInput)
Note:
Once we know the code is functioning the way we want, this print statement should be deleted or commented out.
After running the code, we notice the result would look better if some space were given after each question. Let’s add escaped characters to the strings passed to our input() functions. Note that we are editing the code in steps 3 and 4, not adding new lines of code:
originalBill = input(‘Please enter the bill amount:n>>’)
tipInput = input(‘Please enter the percent you would like to tip:n>>’)
Result:
Please Enter the bill amount:
>>
Note:
The user is now typing at what looks like a prompt.
Now we have two numbers stored in variables. However, they are currently strings and we can’t use math operators on strings. So, let’s cast the two numbers as floats and store the results in new variables:
billFloat = float(originalBill)
tipFloat = float(tipInput)
We can now write a math equation to find the total amount. Remember that the tip percent must be converted to a decimal:
total = billFloat + (billFloat * tipFloat * .01)
Now we can return our results to the user:
print(‘Your total bill with a’, tipInput, ‘percent tip is: $’, total)
We could end here, but after testing our code a few times, we might notice that the total often has too many decimals to represent a dollar amount. The easiest way to correct for this is to use Python’s format() function. That code would look like this:
print(‘Your total bill with a’, tipInput, ‘percent tip is:’, ‘${0:,.2f}’.format(total)

Categories
MySQL

Please enter annual interest rate (example 5.2 for 5.2%):

Learning Goal: I’m working on a mysql question and need guidance to help me learn.
Submission: FirstNameLastNameHomework1.py file
Start your script with the comment #FirstName LastName Homework1, save the file as FirstNameLastNameHomework1.py, and submit it to blackboard.
#Part A
Copy this line of code as is, do not modify the line directly:
pagination = ‘Page 1 of 12’
Find the substring ‘12’ in the pagination variable from Step 1 using the index() function and save the result to a new variable. Name the new variable what you want.
Cast the variable from step 2 to an int datatype and print the result.
Part A Example (if your code works correctly, this should be the result):
12
#Part B
Calculate the average and variance for 4 marathon runners. If you do not remember how to calculate variance, you can follow the example on this very simple website (do not confuse Standard Deviation with Variance).
Copy this code as is:
runner1 = 300.25 #Finish time in Minutes
runner2 = 260.75
runner3 = 315.75
runner4 = 245.25
average = ????????????????????????????????
print(‘Average:’, average)
Replace the question marks on line 5 of Step 1 with the appropriate equation to determine the average using the 4 runner variables.
Add the following code:
variance = ????????????????????????????????
print(‘Variance:’, variance)
Replace the question marks in the code from Step 3 with the appropriate equation to determine the variance.
Part B Example:
Average: 280.5
Variance: 816.3125
#Part C
Frequently in business, we want to calculate the future value of an investment or loan after interest has compounded. The equation to do this is:
A = P( 1 +r/n)nt
Where A is the future value,
P is the principal (initial amount invested or borrowed)
r is the annual rate of interest as a decimal
n is the number of times the interest rate compounds per year
t is the term of the loan or the number of years before it is repaid
We want to build a calculator that will:
Ask the user to input the values for the variables above;
Perform the calculation; and
Print the following string to the user:
In [t] years, at the interest rate of [r] compounded [n] times per year, the initial amount of [P] will be worth [A]
Example:
In 5 years, at the interest rate of 5% compounded 4 times per year, the initial amount of $5000.00 will be worth $6410.19
Note that the figures in this string have been formatted to include only two decimals and a dollar symbol.
You can check the results of your script by using an online future value calculator, like this one. If you do so, leave the annual addition amount at 0 and select ‘start’ as the ‘Make additions at’ option.
Part C Example:
Please enter principle:
4000
Please enter annual interest rate (example 5.2 for 5.2%):
5.2
Please enter the term in years:
5
Please enter number of times the interest will compound per year:
365
In 5.0 years, at the interest rate of 5.2% compounded 365 times per year, the initial amount of $4,000.00 will be worth $5,187.62.
Extra challenge:
If this assignment was easy, you can also calculate the amount paid to interest and print it to a new line. Extra challenges are not necessary to receive full credit on homework and labs.
Extra Challenge Output example:
In 5.0 years, at the interest rate of 5.2% compounded 365 times per year, the initial amount of $4,000.00 will be worth $5,187.62.
$1,187.62 will be paid in interest.

Categories
MySQL

Submit the word document with sql statements and resulting tables on blackboard.

Learning Goal: I’m working on a mysql question and need guidance to help me learn.
The purpose of this assignment is to introduce you to a relational database management systems, including the concept of organizing data in form of tables (or as they are called “relations”) and how to retrieve data from these tables using SQL (Structured Query Language.)
Before starting this assignment, complete the following tutorials Oracle for Absolute Beginners available under Software packages.
These notes are replication of four articles by
David Njoku
Available at
https://www.red-gate.com/simple-talk/sql/oracle/or…
and links to part 2, 3, 4 therein.

Part 1.
This part introduces the concept of relational database in in which data about related items are kept in tables. Each table represents a set of physical or logical objects, with each row describing one member of the set. Further the association between different objects is represented by the concept of relationships which are made explicit using “foreign key” – which means using identifier of the referred to record as a descriptor of referring record.
Part 2.
In this part you are introduced to APEX (Application Express), Oracle’s cloud solution. For individual users, Oracle gives a free workspace, where we can create our own database(s) and test our ideas, do some simple application development, etc., before moving on to more serious development activities. Once you have created your workspace, you will use a script to create tables in the workspace and linking them through foreign keys. The script also enters data into tables, one row at a time.
Note that the original author has removed the link to the script. I have rebuilt the script and made a couple of changes to date fields. The modified script is also available under the tab Software Packages.
Once the data tables are defined and populated, the section walks you through data retrieval using SQL. The basic structured of SQL statement for data retrieval is
SELECT {Columns}
FROM {Tables}
WHERE {Conditions};
Of course, we can include additional clauses. As database users, our job becomes translating a natural language expression into a SQL statement. This part of the tutorial includes only single table queries

Part 3.
In this part you will use SQL statements that involve making changes to data in a table:

INSERT INTO {Table} {Column-name, Value}
UPDATE {Table} SET {Column} = {Value}
DELETE FROM {Table}
WHERE {Conditions}
Once you have familiarized yourself with these operators, make sure to complete the practice steps.
Part 4.
When we retrieve data from multiple tables, we have to concatenate rows from each table and keep only those rows in the result table that satisfy specified conditions. One such condition is “JOIN” when the value of matching fields from the two tables are same. In case of inner joins there must be matching rows in both tables whereas in case of outer joins we also additionally keep rows that have no matching rows in the other table.

WORK FOR ASSIGNMENT SUBMISSION
Once you have completed the tutorial, complete the following steps:
Insert at least three rows into table FRIEND_NAME including a row with Friend_ID =10 and First_Name and Last_Name as you own first name and last name.
Insert two or more rows in the table ADDRESS. One of the rows should be 140 W. 62nd Street, New York, NY 10023.
Insert three or more rows in table PHONE_NUMBER with PHONE_ID as 10,11,12. For PHONE_ID 11 use country code as 1, area code as 646, first three digits as 636 and last four digits as last four digits of your Fordham student-id.
Insert rows into tables FRIEND_PHONE and FRIEND_ADDRESS to associate friends with specific addresses and phone number. Associate the row in FRIEND_NAME with FRIEND_ID 10 (with your own name) with the Phone_id = 11 and Address_Id = 3. Make sure that each Friend is associated with at least one address and one phone. For starting date, use the current date (i.e. the date on which you do the work.)
Use SQL statements to retrieve all rows from all five tables for which you have made changes. With each statement copy the query itself and query results to a MSWord document.

If necessary, reduce column width of each column to about 1” so that all columns lie within the left and right margins. You may also want to consider using landscape orientation.
Use SQL statement to retrieve name (First_Name and Last_Name) and current address (House_Number, Apartment. Street, City, State, Zip) for a new friend that you have entered (under the first bullet point.) Add the SQL statement and the query result to the Word document.
Use a SQL statement to retrieve name and phone number of a new friend that you have entered (under the first bullet point.) Add the SQL statement and the query result to the Word document.
Submit the word document with SQL statements and resulting tables on Blackboard. Your submission should have 7 SQL queries and their results
Appendix SAMPLE OUTPUT

SELECTPHONE_ID, COUNTRY_CODE, AREA_CODE, PHONE_NUMBER
FROM PHONE_NUMBER;
PHONE_ID
COUNTRY_CODE
AREA_CODE
PHONE_NUMBER
1
1
212
5550123
2
1
212
5554569
3
1
212
5556567
4
1
212
5554444
5
1
212
5553734
6
1
212
5553847
7
1
212
5555467
8
1
212
5559686
SELECT *
FROM FRIEND_NAME;
Insert the result here as a table
SELECT *
FROM ADDRESS;
Insert the result here as a table