Describe a specific scenario, situation, or application where using a foreign ke

Question

• Describe a specific scenario, situation, or application where using a foreign key would be necessary.
• Explain your reasons, including the characteristics of the data, that necessitate the foreign key.
• Explain an application requirement that would necessitate the use of a view.

• Suggest three DDL and three DML commands that you believe are most important, and provide an example of their use.
• Explain what each DDL and DML command does for each SQL statement that you have suggested.

• Suggest three DDL and three DML commands that you believe are most important, and provide an example of their use.
• Explain what each DDL and DML command does for each SQL statement that you have suggested.

Explanation / Answer

1)Put the foreign keys on the database. Even if you validate the data in the application before you save it the FK's are a good piece QA backup. For a first approximation, applications always have data issues. Leaving controls like this out of the system just invites failure modes where data gets corrupted silently.

There's nothing like working in data warehousing for a few years to see this in action. You spend your time picking up the pieces after noddy mistakes by application developers who thought they could enforce data integrity in the application code. Spend any time doing this and you will conclude that application managed data integrity is little more than a conceit.

In addition, the query optimiser can use foreign keys to infer things about table joins, so FK's will result in more efficient query plans.

There are plenty of other benefits to foreign keys as well. Do everyone a favour - put the FK's on the database.

2)The most important DDL statements in SQL are:
CREATE TABLE - creates a new database table

we want to create a table called "Persons" that contains five columns: P_Id, LastName, FirstName, Address, and City.

We use the following CREATE TABLE statement:

CREATE TABLE Persons
(
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

ALTER TABLE: To modify the structure of a table.

we want to add a column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons
ADD DateOfBirth date

DROP TABLE: To delete a table.

drop table myemployees_ts0211;

this will delete all the row and table from table myemployees_ts0211.

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects