Who needs a Database?
Redundancy: It refers to storing the same data in more than one place in the database
Primary Key: constraint uniquely identifies each record in a database table. Primary keys must contain unique values. A primary key column cannot contain NULL values. Each table should have a primary key, and each table can have only ONE primary key.
SQL: is a standard language for accessing databases.
Foreign Key: is a column or combination of columns that is used to establish and enforce a link between the data in two tables. You can create a foreign key by defining a FOREIGN KEY constraint when you create or modify a table.
Entities: something that the database is concerned with, about which data can be stored, and which can have relationships with other entities
Gathering information:
Requirements: this is something a database must do. For instance, if must keep track of tutor and classes for which they can tutor
Data Integrity: It refers to the accuracy and the correctness of the data in the database
Reports: A document, paper or electronic used to display summarized or formatted data
Work Shadowing: It means following and observing persons as they go through their work routine
Requirements and business rules:
Requirements: Something the database must do in order to meet the business needs of an organization
Data Requirements: This refers to the attributes the database must contain in order to store all the information an organization needs for its activities. (To record point of sale transaction)
Report Requirements: most database need to generate several different kind of report, summary information often gathered from several different entities
Access & Security: An essential requirement of most databases is to develop a security schema that determines who has access to what data. Anyone without the proper authentication credentials should be excluded
Candidate Key: A key that consists of more than attribute
Database Design:
One to one: one to one relationship is rare, but it can be useful. One to one relationship specifies that for each row in the primary entity, there can be one and no more the one related record in the secondary entity. In one-to-one relationship, the primary key of the first entity is often the primary key of the second entity
One-to-many: means that for each record in the primary entity there can be many associated record in the secondary or child entity
Many-to-many: means the each record in the primary entity can have many related records in a second entity and each record in the second entity can have many related records in the primary entity.
1. Type of entities
a. Domain entities: Are entities that related directly to the business of the database (track customer order, inventory)
b. Linking entities: Are used to resolve many-to-many relationships into two one-to-many relationships
c. Lookup entities: Are essentially utility entities. They store list of data the other tables need to lookup (Names, States Country names or abbreviations)
d. Weak entities: Is an entity that is dependent on another entity for its meaning
Normalization and Design Review:
Deletion Anomalies: Where deleting one piece of data inadvertently causes other data to be lost
Normal Forms: Each normal form is a set of rules designed to reduce or eliminate various anomalies
First Normal Forms: It removes all repeating groups or arrays (entities are, every attribute represents only one valve, there are no repeating groups or arrays, each row is unique)
Third Normal Forms: It refers to removing transient dependencies.
Physical Design
1. Choosing DBRS:
a. Compatibility with your network and operating systems
b. Hardware and software requirements for the DBRS
c. Features of the DBRS in relation to your database requirements
d. Familiarity and expertise in DBMS for database developers and IT personnel
e. Price and licensing requirements
f. Product reliability and support
Creating the Database:
Physical Design: Database design adapted to the features and limitations of a particular RDBMS
Data Type: The column specification that determines what kind of date can be stored in that column, character versus numeric or date, for example

String and Character Type: char(n) variables store fixed-length character strings consisting of exactly n characters (and, therefore, n bytes). They are limited to 8,000 characters in size. nchar(n) variables store fixed-length Unicode character strings consisting of exactly n characters (and, therefore, 2*n bytes). They are limited to 4,000 characters in size. varchar(n) variables store non-fixed length character strings consisting of approximately n characters. They consume l+2 bytes of space, where l is the actual length of the string. They are limited to 8,000 characters in size. nvarchar(n) variables store non-fixed length Unicode character strings consisting of approximately n characters. They consume 2*l+2 bytes of space, where l is the actual length of the string. They are limited to 4,000 characters in size. varchar(max) variables store non-fixed length character strings consisting of up to 1,073,741,824 characters. They consume l+2 bytes of space, where l is the actual length of the string. nvarchar(max) variables store non-fixed length Unicode character strings consisting of up to 536,870,912 characters. They consume l*2+2 bytes of space, where l is the actual length of the string. text and ntext variables store up to 2GB of text data (ANSI and Unicode, respectively), but cannot be used in many text operations. Therefore, they are usually only used to support legacy applications and have been replaced by the varchar(max) and nvarchar(max) data types.
Nulls: Represents the absence of a value. A null value is unknown
SQL Management Studio
Referential Integrity: This exists when every foreign key relates to an existing primary key. There are no orphan records in child tables that have no reference in a parent table
SQL: The programming language used to manipulate data and data objects in a relational database
Basic SELECT statement: (SELECT StudentLastName, StudentFirstName, StudentEmail FROM Student)
Aliasing: A substitute name for a column or table (StudentFirstName AS [First Name])
LIKE clause: ( select ItemName, ItemPrice FROM Inventory WHERE InventoryName LIKE %ball%)
Testing the Database, clause
Functions
Scalar Function: these operating only on a single row
Aggregate Function: these operating on sets of row
DISTINCT with aggregate function ( SELECT COUNT (StudentKay) AS [Total] FROM Session
Joins: allow the user to reunite or join elements the have been split into a single set. The process of normalization breaks tables into smaller and more focused tables
Equi join: A join using the = sign to specify relations an older alternative to the INNER JOIN syntax
Update: Update SecA_Bal SET Name = ‘Jim C. Rocker’, Balance = -1.00 WHERE Customer# = 1311;
Deletes: DELETE FROM Persons WHERE LastName='Tjessem' AND FirstName='Jakob'
Is it secure?
Authentication in sql server: The process of determining the user is in fact who he or she claim to be
Authorization in sql server: This is granting the authenticated user permissions on database objects
Roles: It is a collection of related permissions
Schema’s: Object ownership in a database