Tables, Normalization, Relationships

Question 1

table constraints

Answer 1

column attributes that restrict data

Question 2

table constraints : PRIMARY KEY

Answer 2

a column whose values uniquely identify each row or particular entry in a table (for a table of persons a social security number would be a good primary key because they should be unique for each person)

-Primary Key columns must have a value in each row, and those values must be unique to the table

Question 3

table constraints : FOREIGN KEY

Answer 3

a column in one table that references a column in another table

Question 4

Composite key

Answer 4

If a group of columns are combined to uniquely identify a row, this is called a composite key.

-it is two or more columns acting together as a primary key

Question 5

table constraints : UNIQUE KEY

Answer 5

a column whose values must be unique in each row.

-example: email addresses for people. not two people can have the same email addresses.

Question 6

table constraints : NOT NULL

Answer 6

The column must have a value in each row

Question 7

What is the difference between a primary key and a unique key?**

Answer 7

Primary key will not accept NULL or duplicate values whereas Unique key can accept one NULL value. A table can have only primary key whereas there can be multiple unique key on a table. A Clustered index automatically created when a primary key is defined whereas Unique key generates the non-clustered index.

Question 8

table constraints : CHECK

Answer 8

the column values must meet some criteria

ex: a requirement that the ages of all rows in a table of people be above 0

Question 9

What is the difference between a primary key and a foreign key?**

Answer 9

primary key is a column whose values uniquely identify each row or particular entry in a table
-no nulls or duplicates allowed

a foreign key is a column in one table that references a column in another table

Question 10

Database normalization

Answer 10

Database normalization is the process of designing your database in such a way that
you have no composite columns,
little to no redundant data in a table, and that your data dependencies/relationships make sense.

There are as many as six or seven stages of normalization, but generally a database is acceptable if it reaches the 3rd stage, called Third Normal Form

Question 11

Join Table**and what are the types?

Answer 11

a join table or an association table

A JOIN is a means for combining columns from one (self-join) or more tables by using values that are common to each. ANSI-standard SQL specifies five types of JOIN : INNER , LEFT OUTER , RIGHT OUTER , FULL OUTER and CROSS .

Question 12

Normalization

Answer 12

-it is driven by business requirements

Question 13

TCL : Transaction Control Language

Answer 13

-The process of making a change to your data permanent is called committing, and most databases have a feature called autocommit turned on by default

Question 14

Transaction Control Language : COMMIT, SAVEPOINT, ROLLBACK

Answer 14

Use SAVEPOINT command to save a “snapshot” of your database at any point while executing a series of commands.

You can have multiple save points with different names, or you could “move” your existing save point by reusing it.When you realize that a mistake has been made, you can use the ROLLBACK TO command to revert any changes that have been made to a database since a specified save point.

You cannot roll back to a period before a save point was made, and you cannot rollback or undo a rollback action.
Additionally, you cannot rollback past a commit statement

Question 15

Joins and Unions

Answer 15

-Joining two tables requires that each table have a column that tracks the same data.
(inner joins, left join, right join, outer join)

• Unions: a combination of two distinct select statements in a single result set.
• the first column of your first SELECT statement will be lined up with the first column of your second SELECT statement, and so on.For this reason, your UNION statement must obey two rules: First, the number of columns returned by both SELECT statements must be the same.Second, the data types of each column in the first SELECT statement must match the data types of each column in the second.

Question 16

As keyword

Answer 16

used to specify an alias

Question 17

Procedures and Functions

Answer 17

-A function in SQL is a series of SQL statements that accepts multiple inputs, and returns a single result (a scalar value)

Question 18

User defined function

Answer 18

CREATE FUNCTION function_name ( input type 1);
return type

• first you use the CREATE FUNCTION statement, followed by the function’s name, and then the input parameters and their types between parenthesis.Then you state the return type of the function – does it return an INTEGER, a NUMBER, a VARCHAR, etc.The actual logic goes between the BEGIN and END statements, and at some point in there you RETURN a value.

Question 19

Stored procedure

Answer 19

A stored procedure is similar to a function in that it’s a series of SQL statements that can be invoked by name.

-can return multiple output values

Question 20

** differences between a stored procedure and a function*****

Answer 20

The differences between a stored procedure and a function is a line that is blurred to different degrees by different databases, but usually a stored procedure can return multiple output values or even multiple columns or tables.
A stored procedure can also often be invoked directly by code written in other programming languages, like Java or Python.

-sits in the database rather than being sent to the database. provides safety and security.

Stored procedures can’t be part of a statement or expression the same way functions can.Instead, you have to explicitly run a stored procedure by using the CALL or EXECUTE command.

-best to use stored procedures