这是一篇来自澳洲的关于Introduction to Data Management, Analysis and Security中的Lab 08 – Introduction to SQL的数据库代写，需要演示对数据库系统和体系结构、数据模型和声明性查询语言的概念性理解，以及定义、查询和操作关系数据库，并且将他们的知识应用到一个给定的问题领域

Aim

Our aim in this lab is to strengthen understanding of key concepts from lecture including data types, DB models, relational model and DBMS. A SQLite database will be the practical instance used to learn to create a database, query and display entires in relation in a meaningful and informative manner using filters and creating calculated fields. We will then practise drawing inferences from this data.

Question 1: Data types and database models

While our previous work in data analysis and machine learning in this course has provided us with a very loose interpretation of data structuring, we won’t be able to apply the same rules for defining data going forward. As discussed in the lectures, data can be classified based on it’s characteristics to aid in storage,retrevial and other operations. In the case of Databases, you must learn to first crawl before walk, and walk before run. Therefore, we will be starting with determining the classification of data, before moving onto the more code heavy questions.

Q1.1: Data types: warm-up quick classification

Like starting gym work with stretches, let’s make our brain start spinning! Please review the lecture slides regarding the characteristics of data, then complete the exercise below as fast as possible. No cheating by looking back at the slides!

Please indicate which data type each example below belongs to:

Your options are:

Option   Identifier

Structured  1

Semi-Structured  2

Unstructured  3

# Enter Your Number in the `type` column

| Example               |type         |

| ——————— |————- |

| `Spreadsheets`       | 1.           |

| `Server logs`         | 1.           |

| `Tweets`             | 3.           |

| `Postgre SQL`         | 1.           |

| `titanic.csv dataset` | 2.           |

| `JSON documents`     | 2.           |

| `Mongodb`             |     2.     |

| `Email`               |     3.     |

| `Echo360 recordings` |     3.     |

| `Chiefley catalogues` |     1.     |

| `XML documents`       |     2.     |

| `ANU homepage content`|     3.     |

There will always be the existential question of “why?”. Check with some of your fellow students and your tutor, and see if there are any inconsistencies. Make your case with your colleagues and see if you can come to a conclusion.

Q1.2: Data types: Existence is reason

Though structured data is organised and the easiest to query and analyse, the other two data types have their own use case. Come up with a scenario where a data analyst would prefer semi-structured data over structured data in his/her daily job. You are more than welcome to discuss this with your neighbours.

### Your answer here

Q1.3: The Data Runway: Database models

Move over Gigi Hadid and Kendall Jenner, data is the new queen of the runway.

Modelling a database is one of the key ways of ensuring your DB is fit for purpose and meets your requirements. This can be performed at many stages of the development process for any software development and integration (this is discussed in COMP3120 (https://programsandcourses.anu.edu.au/course/comp3120)), and it is therefore vital to be able to visualise and model the various aspects of your data.

We are interested in 3 key points of data modelling, which we want you to discuss here. Your tasks are as follows:

• Compare and Contrast the characteristics of a Conceptual , Logical and Physical data model.
• Find an image or set of images online that highlight the differences between these items and discuss how these images show the unique characteristics of each model.

You are welcome to discuss this with your tutor.

### Your answer here

By this point, you should have some basic idea of data management in the bag. Now we can move onto the underlying logic behind database systems such as SQL.

Question 2: Relational Algebra: why and how

Relational Algebra (https://www.tutorialspoint.com/dbms/relational_algebra.htm) (RA), first proposed by Edgar F. Codd (https://en.wikipedia.org/wiki/Edgar_F._Codd), is considered the basis for database query languages. In Question 2, we are going to dicuss why it is important,why we need it, and we’ll practice writing relational algebra statements.

Q2.1: SQL not enough?

Before we get stuck into the depths of Relational Algebra (RA), lets discuss the high level ideas behind why we might consider relational algebra to be useful.

Write down some notes for the following questions:

What is the difference between procedural and declarative languages?

• Which type does SQL belong to and why?

If we hand over the SQL statement directly to machine, what are some possible problems?

• How can RA address these problem?

Discuss with your neighbours and put your ideas down below.

### Your ideas here

Q2.2: Pencil & eraser ready? Here comes RA exercise

After discussing Q2.1, you should be ready to feel the power of RA. The below exercise is aimed at increasing your confidence with writing RA statements.

First consider a database with below schemas:

relation schema description

Person ( name, age, gender ) name is a key

Frequents ( name, pizzeria ) (name, pizzeria) is a key

Eats ( name, pizza ) (name, pizza) is a key

Serves ( pizzeria, pizza, price ) (pizzeria, pizza) is a key

Secondly, recall the function items from the lecture slides. While we aren’t going to fill in everything for you, the following symbols may be of use:

• Π
• σ
• ρ
• ⋈

(You will find the meaning behind these symbols in the lecture slides)

Using these symbols and the database above, answer the following questions:

Note: You may write these down instead of putting them in the notebook. Up to you!

1.Find all pizzerias frequented by at least one person under the age of 18.

# Your answer here

2.Find the names of all females who eat either mushroom or pepperoni pizza (or both).

# Your answer here

3.Find all pizzerias that serve at least one pizza that Amy eats for less than $10.00.

# Your answer here

4.For each person, find all pizzas the person eats that are not served by any pizzeria the person frequents. List all such person (name) / pizza pairs.

# Your answer here

Question 3: Welcome the SQLite

The lecture slides provide a good description of a DBMS

-Database Management System (DBMS) Consists of interrelated data and software for analysing the data

There’s so much more to say about DBMS. It implements one of the database model, does the heavy job of storing 1s and 0s and provides user convinient interface to do creation, updating, quering etc. Let’s put all these buzzes in a context – SQLite!

Detour: SQLite

You may be wondering how we are planning on teaching SQL concepts without actually using a “real” database. While many DBMS such as PostgreSQL (https://www.postgresql.org) exist, there is additional overhead of installing items such as this, getting you used to them and getting them to interact with a Jupyter Notebook (or Python script, etc). While other courses (such as COMP2400 (https://programsandcourses.anu.edu.au/course/comp2400)) will use PostgreSQL or other items, we are going to go with the simple option as this is an introductory course. Therefore, enter SQLite!

What is SQLite ?

……