BEST代写-线上编程学术专家

Best代写-最专业靠谱代写IT | CS | 留学生作业 | 编程代写Java | Python |C/C++ | PHP | Matlab | Assignment Project Homework代写

数据库代写|COMS W4111: Introduction to Databases Spring 2023, Sections 002 Take Home Midterm

数据库代写|COMS W4111: Introduction to Databases Spring 2023, Sections 002 Take Home Midterm

这是一篇来自美国的关于数据库介绍,带回家的期中考试数据库代写

 

Overview

Instructions

You may not use late days.

You have one week to complete the take home portion of the midterm. All of the work must be your own, you may not work in groups or teams. You may use outside sources so long as you cite them and provide links.

Points will be taken off for any answers that are extremely verbose. Try to stay between 2-3 sentences for definitions and 5 sentences for longer questions.

You may post privately on Ed or attend OH for clairification questions. TAs will not be providing hints.

Environment Setup

Note: You will need to change the MySQL userID and password in some of the cells below to match your configuration.

Environments

Different applications and different scenarios use different ways to interaction with databases.

We use three different connection/interaction models to give students experience with the various options.

ipython-SQL

In [1]:

%load_ext sql

In [2]:

#

# Set the userid and password for connecting to your instance of SQL.

#

mysql_user = “root”

mysql_password = “dbuserdbuser”

mysql_url = f”mysql+pymysql://{mysql_user}:{mysql_password}@localhost”

print(“Your connection URL is”, mysql_url)

Your connection URL is mysql+pymysql://root:dbuserdbuser@localhost

In [18]:

#

# Connect. See the ipython-sql documentation for the $variable syntax.

#

%sql $mysql_url

SQL Alchemy and Pandas

In [4]:

#

# Yes, I know the cool kids import as pd. I am not cool.

#

import pandas

In [5]:

#

# Pandas SQL operations require a SQL Alchemy engine.

#

from sqlalchemy import create_engine

In [19]:

sql_engine = create_engine(mysql_url)

pymysql

In [7]:

import pymysql

In [20]:

pymysql_con = pymysql.connect(

user= mysql_user,

password= mysql_password,

host= “localhost”,

port= 3306,

autocommit= True,

cursorclass= pymysql.cursors.DictCursor)

Data Loading

Classic Models

We will use the Classic Models (https://www.mysqltutorial.org/mysql-sample-database.aspx) sample database for many of the questions on this exam.

The directory containing this notebook contains a file classic-models-sample.sql .

Load the data:

Open the file in DataGrip using File -> Open dialog.

Select all of the text/SQL in the file.

Click the green arrowhead to run the files contents.

Running the following queries will test if the load worked.

In [10]:

%sql use classicmodels;

* mysql+pymysql://root:***@localhost

0 rows affected.

Out[10]:

[]

In [11]:

%sql show tables;

* mysql+pymysql://root:***@localhost

8 rows affected.

Out[11]:

Tables_in_classicmodels

customers

employees

offices

orderdetails

orders

payments

productlines

products

In [12]:

%sql select count(*) as count from orders join orderdetails using(orderNumber)

* mysql+pymysql://root:***@localhost

1 rows affected.

Out[12]:

count

2996

Lahman’s Baseball Database

You previously loaded information from Lahman’s Baseball Database. (https://www.seanlahman.com/)

If you have not done so, the following code will load the data into a new schema lahmansdb_midterm .

In [22]:

%sql create schema lahmansdb_midterm

* mysql+pymysql://root:***@localhost

1 rows affected.

Out[22]:

[]

In [25]:

people_df = pandas.read_csv(“./People.csv”)

people_df.to_sql(“people”, schema=”lahmansdb_midterm”, con=sql_engine,index=False, if_exists=”replace”)

Out[25]:

20370

In [26]:

batting_df = pandas.read_csv(“./Batting.csv”)

batting_df.to_sql(“batting”, schema=”lahmansdb_midterm”, con=sql_engine,index=False, if_exists=”replace”)

Out[26]:

110495

In [32]:

pitching_df = pandas.read_csv(“./Pitching.csv”)

pitching_df.to_sql(“pitching”, schema=”lahmansdb_midterm”, con=sql_engine,index=False, if_exists=”replace”)

Out[32]:

49430

  • This will test the data loading.

In [29]:

%sql select count(*) as people_count from lahmansdb_midterm.people;

* mysql+pymysql://root:***@localhost

1 rows affected.

Out[29]:

people_count

20370

In [30]:

%sql select count(*) as batting_count from lahmansdb_midterm.batting;

* mysql+pymysql://root:***@localhost

1 rows affected.

Out[30]:

batting_count

110495

In [33]:

%sql select count(*) as pitching_count from lahmansdb_midterm.pitching;

* mysql+pymysql://root:***@localhost

1 rows affected.

Out[33]:

pitching_count

49430

Written Questions

W1

Question

Define the concept of immutable column and key.

Why do some sources recommend that a primary key should be immutable?

How would to implement immutability for a primary key in a table?

Answer

Type Markdown and LaTeX: 𝛼 2

W2

Question

Views are a powerful concept in relational database management systems. List and briefly explain 3 benefits of/reasons for creating a view.

Answer

Type Markdown and LaTeX: 𝛼 2

W3

Question

Briefly explain the concepts of procedural language and declarative language. SQL is primarily a declarative language. SQL added procedure language capabilities in functions, procedures and triggers. What is a reason for this addition?

Answer

In [ ]:

W4

Question

The following diagram is a simple representation of the architecture of a Jupyter notebook using MySQL. Is this a two-tier architecture or a three-tier architecture? Explain your answer briefly.

Answer

Type Markdown and LaTeX: 𝛼 2

W5

Question

Consider a US Social Security Number. An example is “012-34-6789”.

The data type is character string.

The relational model requires that columns (attributes) are from a domain.

Use the Social Security Number example to explain the difference between a type and a domain.

Answer

Type Markdown and LaTeX: 𝛼 2

W6

Question

Briefly explain the differences between:

Database stored procedure

Database function

Database trigger

Answer

Type Markdown and LaTeX: 𝛼 2

W7

Question

Briefly explain:

Natural join

Equi-join

Theta join

Self-join

Answer

Type Markdown and LaTeX: 𝛼 2

W8

Question

Briefly explain the difference between a unique (key) constraint and a primary key constraint?

Answer

Type Markdown and LaTeX: 𝛼 2

bestdaixie

评论已关闭。