2NF Example: Student Project Relation

Database Relations

• In the Student_Project relation, the prime key attributes are Stu_ID and Proj_ID.
• Non-key attributes are Stu_Name and Proj_Name, which must be dependent on both prime key attributes.

2NF (Second Normal Form)

• A relation is in 2NF if it meets the following conditions:
  • The table should be in 1NF.
  • There should be no partial dependency.
• In 2NF, non-key attributes must be dependent on both prime key attributes.
• Candidate Keys: {Stu_ID, Proj_ID}
• Non-prime attributes: Stu_Name, Proj_Name

2NF Application

• Breaking the relation into two tables eliminates partial dependency.

Database Security

• Database security refers to the collective measures to protect and secure a database or database management software from illegitimate use and malicious threats and attacks.
• It includes a multitude of processes, tools, and methodologies to ensure security within a database environment.
• Database security covers and enforces security on all aspects and components of databases, including:
  • Data stored in the database
  • Database server
  • Database management system (DBMS)
  • Other database workflow applications

Database Security Planning and Implementation

• Database security is generally planned, implemented, and maintained by a database administrator and/or other information security professionals.

Normal Forms

• The evolution of normalization theories is illustrated below:
  • 1st Normal Form (1NF)
  • 2nd Normal Form (2NF)
  • 3rd Normal Form (3NF)
  • Boyce-Codd Normal Form (BCNF)
  • 4th Normal Form (4NF)
  • 5th Normal Form (5NF)
  • 6th Normal Form (6NF)

1NF (First Normal Form)

• 1NF states that the domain of an attribute must include only atomic values.
• An attribute's value in a row must be a single value from the domain of that attribute.
• Atomic values pertain to the built-in atomic data types.

1NF Rules

• A relation is in 1NF if it satisfies the following conditions:
  • The domain of an attribute must include only atomic values.
  • The value of any attribute in a row must be a single value from the domain of that attribute.

Functional Dependency

• A functional dependency is a concept that specifies the relationship between two sets of attributes, where one attribute determines the value of another attribute.
• Example: StudentID → Semester.

Partial Dependency

• If a non-prime attribute can be determined by the part of the candidate key in a relation, it is known as a partial dependency.

Authentication

• Two-factor authentication (tokens, certificates, biometrics, etc.) is preferable whenever possible.
• Strong username/password policy (minimum length, character diversity, obscurity, etc.) should be enforced.
• Preventing authentication mechanisms should be integrated with enterprise directory infrastructure.
• Directory Integration enables a user to use a single set of log-in credentials for multiple databases and applications.

Backup and Data Exposure

• Backup database storage media is often completely unprotected from attack, resulting in security breaches.
• Data involved in theft of database backup tapes and hard disks.
• All database backups should be encrypted.
• Preventing data exposure is crucial.
• Encryption of on-line production database information is often suggested.