C# Introduction to Databases

Introduction to Databases

• An entity is the smallest unit of information in a database
• A Table is used to hold one type of data and can be related to other tables
• A column in a table holds one piece of data type shared by all records, also called an “attribute”
• A row holds all data related to one entity, also called a “record”
• A relational database defines how data relate to each other
• A domain constrains a column to a specific data type, i.e. int or string
• A primary key is unique within a table and uniquely identifies a record
• A foreign key is a primary key from another table
• Stored Procedures are methods stored directly in the database that work on data in the database
• They allow you to enforce business rules within your database
• Cardinality defines the type of relationship between a table and a foreign key: One-to-One, One-to-Many or Many-to-Many
• Optionality tells whether the relationship is mandatory or optional
• Referential Integrity makes sure changes in one table don’t negatively affect another table
• Database normalization allow you to optimize your database by minimizing redundancy and dependency
• Structured Query Language (SQL) is a special-purpose programming language designed for querying and manipulating databases
• The Microsoft version is called T-SQL
• There are two types of queries you can make:
• Data Definition Language (DDL) which defines and updates the structure of the data
• Data Manipulation Language (DML) which allows you to create, modify or delete data using four keywords:
• CREATE e.g. CREATE TABLE Employee
• SELECT e.g. SELECT * FROM Customers
• SET e.g. SET ZipCode = “00000” WHERE…
• DELETE e.g. DELETE * FROM Customers WHERE…
• The ACID test defines a valid database transaction
• Atomicity - Each transaction is “all or nothing”
• Consistency – Any transaction should bring the database from one valid state to another
• Isolation – Concurrent transactions are the same as serial transactions
• Durability – Once a transaction is committed, it stays committed
• Standard database design follows the following steps:
• Determine entities involved and create a table for each type
• Determine Primary Key for each table
• Determine non-key attributes
• Determine relationships between tables