Category: Databases

Choose one real-world scenario below and design one database table for it.

Scenario Options (Choose One)

• Patients in a clinic
• Students enrolled in a university
• Employees at a company
• Products sold by a store

---

Part 1: Design Your Table

Create one table with:

• A clear table name
• 46 columns
• One primary key

Example (structure only):

Column Name	Description
StudentID	Unique identifier for each student
FirstName	Students first name
LastName	Students last name
Major	Academic major
Email	Student email

(Your table and columns will vary based on the scenario you choose.)

---

Part 2: Identify the Primary Key

Answer the following:

1. Which column is your primary key?
2. Why is this column a better primary key than:
   • A name?
   • An email or phone number?

---

Part 3: Reflection

In 35 sentences, respond to:

• What problems could occur if your table did not have a primary key?
• Why are primary keys especially important as databases grow?

---

Deliverables

• One page or less
• Table design + written responses
• Clear section labels

Ola Cholkan is a recent Seneca College graduate with a diploma in Business Administration, specializing in Entrepreneurship and Small Business. During her studies at Seneca, she participated in the program – a resource that is dedicated to developing the entrepreneurial mindset by providing knowledge, mentorship, sessions, workspace, access to like-minded peer community, and experiential opportunities. Here she developed an idea to start a small business related to her love of pets, and especially to dogs. According to The Canadian Animal Health Institute ( ), the number of pet dogs owned in Canada as of 2018 was 8.2 million, up from 7.6 million in 2016. Further says that a dog owner in Canada can expect to pay about $1,000 a year to care for one dog of which $400 is spent on food. A 2018 survey indicated that 43% of respondents are willing to pay more for healthy pet food. According to , 1-2% of all dogs have cutaneous adverse food reactions, or food allergies that manifest in skin conditions. With all of this research in hand, Ola decided she would bake homemade healthy dog treats for dogs with allergies, i.e., without meat, dairy products, chicken or wheat, and sell them to existing retail pet stores throughout Ontario such as Pet Valu and PetsMart. Since Ola lives in a small apartment, she needs to rent a larger space with a commercial stove and additional staff to help bake the treats and ship them. In the future she would like to add e-commerce capability to her web site and sell directly to customers online.

Ola uses the following recipe to bake her allergy-free dog treats. Each recipe makes 50 treats. There are 10 treats in each package.

• 1 1/2 cup coconut flour
• 1/2 cup unsweetened peanut butter
• 4 organic eggs
• 1/2 cup coconut oil
• 1 cup + 2 tbsp pumpkin puree

The costs associated with baking these treats are outlined in the Excel case file.

When finished reading the case background, select True and move to the next question.

---

True

---

False

Case 4- worth 4% of your final grade Format: File Upload. Task instructions are included in the attached Excel file located in question 3.

1. Ola Cholkan is a recent Seneca College graduate with a diploma in Business Administration, specializing in Entrepreneurship and Small Business. During her studies at Seneca, she participated in the program – a resource that is dedicated to developing the entrepreneurial mindset by providing knowledge, mentorship, sessions, workspace, access to like-minded peer community, and experiential opportunities. Here she developed an idea to start a small business related to her love of pets, and especially to dogs. According to The Canadian Animal Health Institute ( ), the number of pet dogs owned in Canada as of 2018 was 8.2 million, up from 7.6 million in 2016. Further says that a dog owner in Canada can expect to pay about $1,000 a year to care for one dog of which $400 is spent on food. A 2018 survey indicated that 43% of respondents are willing to pay more for healthy pet food. According to , 1-2% of all dogs have cutaneous adverse food reactions, or food allergies that manifest in skin conditions. With all of this research in hand, Ola decided she would bake homemade healthy dog treats for dogs with allergies, i.e., without meat, dairy products, chicken or wheat, and sell them to existing retail pet stores throughout Ontario such as Pet Valu and PetsMart. Since Ola lives in a small apartment, she needs to rent a larger space with a commercial stove and additional staff to help bake the treats and ship them. In the future she would like to add e-commerce capability to her web site and sell directly to customers online.Ola uses the following recipe to bake her allergy-free dog treats. Each recipe makes 50 treats. There are 10 treats in each package.

   • 1 1/2 cup coconut flour
   • 1/2 cup unsweetened peanut butter
   • 4 organic eggs
   • 1/2 cup coconut oil
   • 1 cup + 2 tbsp pumpkin puree

   The costs associated with baking these treats are outlined in the Excel case file.When finished reading the case background, select True and move to the next question.

   ---

   True

   ---

   False

Continue

Generate SQL statements to address the problems below using the COVID19_DB database. Each problem must be addressed by a single SQL statement. Use the following database design diagram and data dictionary document as a guide in forming your statements:

You must incorporate confirmatory work screenshots into a Microsoft Word assignment document (or equivalent). The following image illustrates an example problem screenshot showing both the SQL statement and confirmatory execution results:

Problem 1: Display the list of COVID-19 vaccines.

Expected Output:

Problem 2: Display the list of COVID-19 vaccines in alphabetical order by vaccine name.

Expected Output:

Problem 3: Display the COVID-19 vaccines that are suitable for children (minimum age < 18 years).

Expected Output:

Problem 4: Which states/jurisdictions have a population between 10,000,000 and 20,000,000? Show both the state/jurisdiction names and populations in alphabetical order by state/jurisdiction.

Expected Output:

Problem 5: Provide a list of the states/jurisdictions with names starting with the letter M. The output needs to show the state/jurisdiction name, capital, and population in alphabetical order by state/jurisdiction name.

Expected Output:

Problem 6: List the size/area for the following states: Pennsylvania, Iowa, Rhode Island, Maine, and Alabama. Show the state/jurisdiction names and size/area values in descending size/area order.

Expected Output:

Problem 7: Show the COVID-19 vaccine distribution dates for dose allocations that exceeded 600,000 for any of the vaccines. The output needs to show both the vaccine distribution dates and dose allocations. The results must be presented in chronological order (i.e., oldest to most recent).

Expected Output:

Problem 8: List the first and second COVID-19 vaccine dose series volumes for children. Show both the dose series and volume in the output. (Hint: Permissible attribute values for BIT data types are ‘TRUE’ and ‘FALSE’.)

Expected Output:

Problem 9: List the dates the number of reported COVID-19 cases exceeded 75,000 for any state/jurisdiction. Show the calendar date and number of reported COVID-19 cases in chronological order (i.e., oldest to most recent).

Expected Output:

Problem 10: List the COVID-19 vaccine ingredients that are in both the child and adult formulations. Show the ingredients in alphabetical order. (Hint: Permissible attribute values for BIT data types are ‘TRUE’ and ‘FALSE’.)

Expected Output:

1. Pengolahan Data Dasar

Fungsi-fungsi yang sering digunakan dalam pengolahan data dasar sebagai berikut:

a. Sort (Mengurutkan)

Digunakan untuk menyusun data berdasarkan urutan tertentu, seperti dari terkecil ke terbesar (ascending) atau sebaliknya.

b. Filter (Menyaring)

Memungkinkan pengguna menampilkan data sesuai kriteria tertentu, misalnya hanya menampilkan nilai di atas 75.

c. Find and Replace

Berguna untuk mencari data tertentu dan menggantinya secara otomatis.

d. Text to Columns

Digunakan untuk memisahkan data dalam satu sel menjadi beberapa kolom, misalnya memisahkan nama depan dan nama belakang.

e. AutoSum / SUM

Menghitung total nilai dari sejumlah data numerik.

f. AVERAGE, MAX, MIN

Masing-masing digunakan untuk menghitung rata-rata, nilai tertinggi, dan nilai terendah dalam sekumpulan data.

g. COUNT dan COUNTA

Menghitung jumlah data, baik yang berisi angka saja (COUNT) maupun semua jenis isi sel (COUNTA).

2. Pengolahan Data Lanjutan

Fungsi-fungsi yang sering digunakan dalam pengolahan data lanjutan mencakup analisis, transformasi, dan visualisasi data. Berikut penjelasannya:

a. Pivot Table

Digunakan untuk menganalisis, meringkas, dan memutar data besar menjadi laporan interaktif berdasarkan kategori tertentu.

b. VLOOKUP, HLOOKUP, XLOOKUP

Digunakan untuk mencari data dalam tabel secara vertikal (VLOOKUP) atau horizontal (HLOOKUP).

XLOOKUP merupakan versi yang lebih fleksibel.

c. IF, IFERROR, dan Fungsi Logika Lainnya

Berguna untuk membuat keputusan bersyarat dalam pengolahan data, seperti memberikan kategori Lulus atau Tidak Lulus.

d. INDEX dan MATCH

Kombinasi fungsi untuk pencarian data yang lebih fleksibel dibandingkan VLOOKUP.

e. Data Analysis Toolpak (di Ms. Excel)

Menyediakan fitur analisis statistik seperti regresi, korelasi, histogram, ANOVA, dan sebagainya.

f. Fungsi Statistik Lanjutan

Seperti STDEV, CORREL, LINEST, untuk menganalisis data secara matematis.

g. Fungsi Text dan Date Lanjutan

Contohnya TEXTJOIN, NETWORKDAYS, DATEDIF untuk manipulasi data teks dan waktu.

h. Visualisasi Data

Menggunakan grafik dinamis seperti charts, sparklines, atau bahkan dashboard interaktif.

Kesimpulan

Pengolahan data dalam aplikasi seperti Microsoft Excel terbagi menjadi pengolahan data dasar dan lanjutan. Pengolahan data dasar berfokus pada pengurutan, penyaringan, dan perhitungan sederhana, sedangkan pengolahan data lanjutan digunakan untuk analisis yang lebih kompleks, pencarian data yang fleksibel, serta penyajian data dalam bentuk visual. Dengan menguasai kedua jenis pengolahan data tersebut, pengguna dapat mengelola dan menganalisis data secara lebih efektif, efisien, dan akurat.

Main Computer Science ke database subjects par madad faraham karta hoon, jismein SQL queries, DBMS concepts, ER diagrams, normalization, relational databases, aur assignments/shorthand notes shamil hain. Main accurate, plagiarism-free aur time par kaam provide karta hoon. Students exams, quizzes aur projects ke liye mujh se help le sakte hain.

Main Computer Science ke database related kaam karta hoon jaise SQL queries, DBMS theory, assignments, quizzes, aur projects. Fast response, clear explanation aur quality work meri priority hai. Agar aapko database concepts samajhne ya assignment complete karne mein madad chahiye to mujh se rabta karein.

1. Identify the Key Entities: Your ERD for a Course Enrollment System should include the following entities: Major, Department, Student, Course, Instructor, Semester, Course Section, Class Schedule, Cart Item, Cart, and Payment.

2. Define Attributes: For each entity, identify essential attributes.

3. Establish Relationships: Clearly show how the entities are related to each other in the system. Indicate Primary Keys and Foreign Keys in each entity.

4. Diagram Requirements: To be consistent with notations (shapes), you can use the following example as a template for the assignment.

don’t make it to much as simple as its showing

The Jurisdiction dataset looks like the following in Microsoft Excel:

Carry out the instructions contained in the following document:

Generate a screenshot similar to that presented in the final step of the exercise document and incorporate it into your Microsoft Word assignment document. This will serve as confirmation of work completion.

Part 2: COVID-19 Database Creation Script

The following documents contain the physical design diagram and data dictionary for the COVID-19 database:

A partially completed COVID-19 database creation script is contained in the following file:

Download the database creation script file to an accessible location on your computer. Open the script file in the Microsoft SQL Server Management Studio application by selecting FILE OPEN FILE in the top menu:

The Open File dialogue will appear. Navigate to the script file, select it, and then click on the Open button.

The script file contents will appear in a new windowpane on the right side of the Microsoft SSMS application. Using the provided physical database design diagram, complete the CREATE TABLE definitions for the following tables: Jurisdiction, CovidCases, and CovidDeaths. Replace the ?????????? tags with the required SQL code per the database design specification. Click on the Save button to update the database creation script file.

Please note that the CovidCases and CovidDeaths tables contain composite primary keys.

Take screenshots of the completed CREATE TABLE definitions for the Jurisdiction, CovidCases, and CovidDeaths tables and incorporate them into your Microsoft Word assignment document. This will serve as confirmation of work completion.

Part 3: COVID-19 Database Implementation

Execute the completed COVID-19 database creation script by opening the file in Microsoft SSMS and then clicking on the Execute button:

After the COVID-19 database is established without error, download the following COVID-19 database population script file to an accessible location on your computer:

Open the database population script in Microsoft SSMS in the same way it was done for the database creation script in the Part 2 assignment work. You do not need to modify the database population script after it is opened in Microsoft SSMS. You just need to click on the Execute button to run the script, which will populate the established COVID-19 database tables with data.

Please note that the database population script takes several minutes to run. The script execution time depends on the performance of the computer running the Microsoft SQL Server and SSMS applications.

Click on the Refresh button () in the top menu to refresh the list of databases in the Object Explorer pane.

Any errors that occur during the execution of the database creation and population scripts are likely due to mistakes in the Part 2 assignment work. Re-visit Part 2 and make the appropriate corrections to the CREATE TABLE definitions for the Jurisdiction, CovidCases, and CovidDeaths tables. After the corrections are incorporated and the database creation script file is saved, re-run both the database creation and population scripts.

Incorporate screenshots of the generated database into your Microsoft Word assignment document. The screenshots need to show the structure of each established COVID-19 database table. This will serve as confirmation of work completion. An example screenshot showing database structures (i.e., tables, attributes, attribute types, and keys) from the Object Explorer pane of SQL Server Management Studio is provided below:

Part 4: Manual Data Manipulation

The COVID-19 database population script executed in Part 3 of the assignment does not contain any data for the VaccineResearcher table. Generate and execute INSERT statements to add the following vaccine researchers:

Researcher 1:

FirstName = Katalin

LastName = Kariko

Title = Lead Researcher

VaccineID = 2

Researcher 2:

FirstName = Hamilton

LastName = Bennett

Title = Senior Director

VaccineID = 1

Please note that you do not need to provide values for the ResearcherID attribute in the VaccineResearcher INSERT statement. The ResearcherID attribute has the IDENTITY specification assigned to it, which means that the ResearcherID value is automatically assigned via sequential numbering.

Take screenshots showing the completed INSERT statements along with confirmation of the data entry into the VaccineResearcher table. Incorporate the screenshots into your Microsoft Word assignment document. The image below provides a screenshot example.

Katalin Kariko has been promoted to Vice President. Perform an update of Katalin Karikos record in the VaccineResearcher table to reflect this. Take a screenshot showing the completed UPDATE statement along with confirmation of the data modification in the VaccineResearcher table. Incorporate the screenshot into your Microsoft Word assignment document. The image below provides a screenshot example.

Hamilton Bennett has retired and is no longer performing vaccine research work. Delete Hamilton Bennetts record from the VaccineResearcher table. Take a screenshot showing the completed DELETE statement along with confirmation of the data deletion in the VaccineResearcher table. Incorporate the screenshot into your Microsoft Word assignment document. The image below provides a screenshot example.

This case uses the admissions database table that was used in cases 1 and 2. You will be asked to create macros using this data. The background to the admissions data is repeated below:

The Joseph L. Rotman School of Management is the University of Toronto’s graduate business school, located in downtown Toronto. Established in 1950, the school offers full-time, part time and executive Master of Business Administration (MBA) programs. Rotman has been ranked as the top business school in Canada for much of the past decades. In 2021 the Rotman school was ranked as the business school in Canada and ranked in the world.

The for acceptance into this school includes the following:

• A minimum GPA of 3.0 in the final year of a bachelors degree from a recognized university.
• A competitive GMAT (Graduate Management Admission Test) score – although there is no minimum GMAT score required, the average GMAT score for 2021 applicants was 669;
• A minimum of 2 years of full-time work experience;
• Two professional references;
• A written essay outlining personal and work related experiences and video interview;
• English language proficiency demonstrated by having attained an undergraduate degree from a recognized English language university, or a TOEFL iBT score of 100, or an IELTS score of 7.0 .

All candidates will be interviewed before they are offered admission to the program; interviews are by invitation only.

You have been hired by the Admissions Officer, Ola Cholkan, to help determine which of the current year applicants should be considered for an interview.

After reading the case background, select True and move to the next question.