DATABASES

Question 1

Schema

Answer 1

determines how data is organized between tables in your database.

Question 2

Database Schema

Answer 2

The structure of a database. (Data is organized into Tables with columns and rows) A blank structure without information.

Question 3

Schema Objects

Answer 3

Parts of a schema (like tables, columns, relationships, data types, keys)

Question 4

What are the 4 benefits to using a Database Schema?

Answer 4

1) Better management via logical groupings for objects
2) Better Accessibility
3) More Security Features
4) Permits ownership transfer between users

Question 5

Database Modeling

Answer 5

Designing the database schema before inputting information.

Question 6

3 Categories of a Database Schema

Answer 6

aka the “three-schema architecture”
1) Conceptual/logical schema (defines entities, attributes, tables and relationships)

2) Internal/physical schema that (defines the actual storage of data and access paths.)

3) External/view schema that (defines different user views.)

Question 7

conceptual or logical schema

Answer 7

describes the structure of the entire database for all the users. It describes the structure in terms of entities and features of the entities and the relationships between them. An Entity Relationship Diagram (ER-D) is usually drawn to represent the logical schema of a database.
-At this level, details about the physical storage and retrieval of data are hidden, and the database structure is described only at a concept level. The software developers work with the database at this level.

Question 8

internal or physical schema

Answer 8

describes the physical storage of the database. It represents the entire database but at a very low level. This means it describes how the data is really stored on disk in the form of tables, columns and records. It defines what data is stored in the database and how.

Question 9

external or view schema

Answer 9

describes the database like an external user would want to see it. This schema only describes the part of the database that the specific user is interested in. It hides the nonrelevant details of the database from a user. For example, a user from the sales department will see only sales-related data in a database. There can be many external schemas of a single database for different users.

Question 10

What 3 Things does a Database Schema Help Data Engineers Do?

Answer 10

1)Maintain a clean set of data in the database related to an application.

2)Avoid reverse-engineering of the underlying data model from time to time.

3)Write efficient queries to retrieve data for reporting purposes, analytics and so on.
-In other words, it prevents you from ending up with a database design that requires a database engineer to do a lot of reverse-engineering down the line, wasting time and effort that leads to increased costs for organizations.

Question 11

ER Modeling (Entity Relationship Modeling)

Answer 11

Graph illustrating relationships between entity types.

Question 12

Logical Database Schema

Answer 12

Refers to the organization of data in tables or entities.

Question 13

Physical Database Schema

Answer 13

How the physical structure of a database is stored on a disk. (or step 2 after making a Logical Database Schema)

Question 14

ER-D

Answer 14

Entity Relationship Diagram

Question 15

Name the 3 Possible Table Relationships

Answer 15

1) One to Many
2) One to One
3) Many to Many

Question 16

Relational Model

Answer 16

The relational model is built around three main concepts which are:

Data, Relationships, and constraints.

It describes a database as “a collection of inter-related relations (or tables)”. It is still a dominant model used for data storage and retrieval. In essence, it is a way of organizing or storing data in a database. SQL is the language that’s used to retrieve data from a relational database.
(The Relational Database Model is the most widely used data model for commercial databases.)

Question 17

What’s another name for a Row?

Answer 17

Record orTuple

Question 18

What’s another name for a Column?

Answer 18

Fields or Attributes

Question 19

What’s another name for a Table?

Answer 19

A Relation: A relation represents a file that stores data

Question 20

What’s another name for the data stored in a column.

Answer 20

an Instance

Question 21

Domain

Answer 21

The domain is a set of acceptable values that a column is allowed to contain. The domain depends on the data type of the column

Question 22

What’s another name for the Primary key?

Answer 22

Relation key

Question 23

Degree

Answer 23

Degree is the number of columns or attributes within a relation. A student table that stores the student’s name, address, phone number and email address would have a degree of four.

Question 24

Cardinality

Answer 24

refers to how many records there are within a particular table in a database. If you have 100 students in your student table, with all their information organized into individual rows, then that table has a cardinality of 100

Question 25

relational integrity constraints

Answer 25

The three conditions that must be met for a relation to be valid. They are called relational integrity constraints and they are:
1) Key constraints
2) Domain constraints
3) Referential integrity constraints

Question 26

key attribute

Answer 26

a key attribute is an identifier that can be used to refer to a record. It must also be unique for each record. For example, you can use the Student ID in the student table as the key. This means that there can’t be two students with the same Student ID. (Cannot be NULL)

Question 27

Candidate Key

Answer 27

An attribute unique to each row of a Table

Question 28

Composite Primary Key

Answer 28

a combination of 2 or more attributes. This key is used when there is no unique rows to label as the primary key.