Primary Key, Foreign Key and Unique Key

SQL Primary Key

A primary key is a column whose values uniquely identify every row in a table. To define a field as primary key, following conditions had to be met :

• No two rows can have the same primary key value.
• Every row must have a primary key value
• Primary key field can't be null
• Values in primary key columns can never be modified or updated.

Primary key values can never be reused. If a row is deleted from the table, its primary key may not be assigned to any new rows in the future. In the examples given below, “Employee_ID” field is the primary key.

SQL Composite Primary Key

A Composite primary key is a  set of columns whose values uniquely identify every row in a table. For example, in the table given above , if "Employee_ID" and "Employee Name" together uniquely identifies a row its called a Composite Primary Key . In this case , both the columns will be represented as primary key.

SQL Foreign Key

When, "one" table's primary key field is added to a related "many" table in order to create the common field which relates the two tables, it is called a foreign key in the "many" table. In the example given below, salary of an employee is stored in salary table. Relation is established via foreign key column “Employee_ID_Ref” which refers “Employee_ID” field in Employee table.

For example, salary of "Jhon" is stored in "Salary" table. But his employee info is stored in "Employee" table. To identify the salary of "Jhon", his "employee id" is stored with each salary record. 

The advantage of using foreign key is that, the data is not getting duplicated. If foreign key concept was not there in RDBMS, entire info of an employee, such as First Name, Last Name, Id etc. had to be stored with each and every salary entry. Another advantage of foreign key is that the editing master entry such as designation, address, etc. wont have any impact on the child table.

SQL Unique Key

Unique key is same as primary with difference being the existence of null. Unique key field allows one value as NULL value. It wont allow duplicate entries.

Concept of Primary Key, Foreign Key and Unique Key is same in all the databases.

RDBMS and DBMS - Concepts and Basics

DBMS

Database management system is a collection of programs that enables user to store, retrieve, update and delete information from a database.

RDBMS

Relational Database Management system (RDBMS) is a database management system (DBMS) that is based on the relational model. Beauty about relational database is that the data stored in them can be retrieved or reorganized in different ways without making changes to the database tables. Data from relational database can be accessed using a API, Structured Query Language (SQL).

SQL

Structured Query Language(SQL) is a language designed specifically for communicating with databases. SQL is an ANSI (American National Standards Institute) standard.

Advantages of SQL

• SQL is not a proprietary language used by specific database vendors. Almost every major DBMS supports SQL, so learning this one language will enable programmers to interact with any database like ORACLE, SQL Server, MySQL, DB2, Postgresql etc.

• Learning SQL is very easy. We can make SQL statements using a predefined set of English words. Since there aren't many SQL keywords, it is very easy to remember them.

• Very complex and precision oriented database operations can be performed using SQL statements. 

Database Definition

A DATABASE is a collection of logically related tables which are stored in a file or set of files. A database is a structured object that consists of data and metadata (Table definitions).

Object-Oriented Data Modeling

1.	The fact that the same operation may apply to two or more classes is called what?
	A. Inheritance B. Polymorphism C. Encapsulation D. Multiple classification

2.

The object-oriented development life cycle is which of the following?

A. Analysis, design, and implementation steps in the given order and using multiple iterations. B. Analysis, design, and implementation steps in the given order and going through the steps no more than one time. C. Analysis, design, and implementation steps in any order and using multiple iterations. D. Analysis, design, and implementation steps in any order and going through the steps no more than one time.

3.	Multiplicity is the same as what concept for an ERD?
	A. Relationship B. Attribute C. Entity D. Cardinality

4.	Composition is a stronger form of which of the following?
	A. Aggregation B. Encapsulation C. Inheritance D. All of the above.

5.	An abstract class is which of the following?
	A. A class that has direct instances, but whose descendants may have direct instances. B. A class that has no direct instances, but whose descendants may have direct instances. C. A class that has direct instances, but whose descendants may not have direct instances. D. A class that has no direct instances, but whose descendants may not have direct instances

Data and Database Administration

1.	Poor data administration can lead to which of the following?
	A. A single definition of the same data entity B. Familiarity with existing data C. Missing data elements D. All of the above.

2.	A traditional data administrator performs which of the following roles?
	A. Tune database performance B. Establish backup and recovery procedures C. Resolve data ownership issues D. Protect the security of the database.

3.	If both data and database administration exist in an organization, the database administrator is responsible for which of the following?
	A. Data modeling B. Database design C. Metadata D. All of the above.

4.	Which of the following is part of an administrative policy to secure a database?
	A. Authentication policies B. Limiting particular areas within a building to only authorized people C. Ensure appropriate responses rates are in external maintenance agreements D. All of the above.

5.	Backward recovery is which of the following?
	A. Where the before-images are applied to the database B. Where the after-images are applied to the database C. Where the after-images and before-images are applied to the database D. Switching to an existing copy of the database

The Internet Database Environment

1.	An intrusion detection system does not perform which of the following?
	A. Tries to identify attempts to hack into a computer system. B. May monitor packets passing over the network. C. May transmit message packets to the correct destination. D. Set up deception systems that attempt to trap hackers.

2.	Features of XML include which of the following?
	A. The tags are used to describe the appearance of the content. B. Addresses the structuring and manipulation of the data involved. C. Governs the display of information in a Web browser. D. All of the above.

3.	What is Hypertext Transfer Protocol (HTTP)?
	A. The protocol to copy files between computers B. The transfer protocol to transfer Web pages to a browser C. The database access protocol for SQL statements D. The hardware/software protocol that limits access to company data

4.	Which of the following is true concerning Web services standards?
	A. Led by only one group. B. Helped by cooperation between companies. C. Not necessary at this time. D. Important so that the web can flourish.

5.	Common Gateway Interface (CGI) is which of the following?
	A. An interface that accepts and returns data that may be written in SQL. B. An interface that accepts and returns data that may be written in any language that produces an executable file. C. A small program that executes within another application and is stored on the server. D. A small program that executes within another application and is stored on the client.

Advanced SQL

1.	What type of join is needed when you wish to include rows that do not have matching values?
	A. Equi-join B. Natural join C. Outer join D. All of the above.

2.	What type of join is needed when you wish to return rows that do have matching values?
	A. Equi-join B. Natural join C. Outer join D. All of the above.

3.	Which of the following is true concerning a procedure?
	A. You do not create them with SQL. B. They do not need to have a unique name. C. They include procedural and SQL statements. D. They are the same thing as a function.

4.	A CASE SQL statement is which of the following?
	A. A way to establish an IF-THEN-ELSE in SQL. B. A way to establish a loop in SQL. C. A way to establish a data definition in SQL. D. All of the above.

5.	Which of the following statements is true concerning routines and triggers?
	A. Both consist of procedural code. B. Both have to be called to operate. C. Both run automatically. D. Both are stored in the database.

Logical Database Design

1.	The entity integrity rule states that:
	A. no primary key attribute may be null. B. no primary key can be composite. C. no primary key may be unique. D. no primary key may be equal to a value in a foreign key.

2.	When mapping a many-to-many unary relationship into a relation which of the following is true?
	A. One relation is created. B. Two relations are created. C. Three relations are created. D. Four relations are created.

3.	If no multivalued attributes exist and no partial dependencies exist in a relation, then the relation is in what normal form?
	A. First normal form B. Second normal form C. Third normal form D. Fourth normal form

4.	A foreign key is which of the following?
	A. Any attribute B. The same thing as a primary key C. An attribute that serves as the primary key of another relation D. An attribute that serves no purpose

5.	A transitive dependency is which of the following?
	A. A functional dependency between two or more key attributes. B. A functional dependency between two or more nonkey attributes. C. A relation that is in first normal form. D. A relation that is in second normal form.

Modeling Data in the Organization

1.	A binary relationship is which of the following?
	A. A relationship between two attributes B. A relationship between two entities C. An attribute with two different relationships D. An entity with two different relationships

2.	If there are two entities with a relationship between them, and one of the entities is completely dependent on the other, then which of the following must exist?
	A. Derived attribute B. Optional one cardinality C. Multi-valued attribute D. Weak entity

3.	A relationship is which of the following?
	A. A person, place, or thing B. Property or characteristic of an entity C. Link between entities D. Weak entity

4.	Which of the following conditions should exist if you want to convert a relationship to an associative entity?
	A. Some of the relationships for the participating entities are "many". B. The resulting associative entity has an independent meaning. C. The resulting associative entity has no attributes. D. All of the above

5.	A good identifier is which of the following?
	A. One that can change over time B. Can be null C. One that is unique D. Be intelligent