Database Design

Question 1

DDL (Database Definition Language)

Answer 1

is a sublanguage of SQL that consists of a set of commands used to define the database, tables, indexes, keys, constraints, and other metadata that comprise a database schema

Question 2

Schema

Answer 2

is a skeletal structure of the database that focuses on the tables and constraints without the data.

Question 3

CREATE TABLE

Answer 3

creates a new table and defines its structure. The columns and data types are required. The Primary Key, Foreign Keys, and other constraints are typically included, but can be added later.

Question 4

DROP TABLE

Answer 4

removes a table

Question 5

ALTER TABLE

Answer 5

can change the structure of a table or add a constraint

Question 6

Sequences

Answer 6

are incrementing numbers that are commonly used as Surrogate Primary Keys. Start at 0, unless given a starting value. Never stops incrementing.

Question 7

Creating a Sequence manually:

Getting the next number manually:

Answer 7

CREATE SEQUENCE custom_seq;
SELECT nextval('custom_seq');

Question 8

DCL

Answer 8

Database Control Language
GRANT
REVOKE

Question 9

Database Control Language

Answer 9

used to administer the database, users, and permissions.

Question 10

GRANT

Answer 10

gives access to a specific action for a resource to a user.

Question 11

REVOKE

Answer 11

removes access to specific action for a resource from a user.

Question 12

Normalization

Answer 12

The process of improving a database design in steps, called forms of normalization

Question 13

Normalization has 4 goals:

Answer 13

1. arranging data into logical groupings such that each group describes a small part of the whole;
2. minimizing the amount of duplicate data stored in a database;
3. organizing the data such that, when you modify it, you make the change in only one place
4. building a database in which you can access and manipulate the data quickly and efficiently without compromising the integrity of the data in storage.