Please enable JavaScript.
Coggle requires JavaScript to display documents.
Database Design and Implementation (Primary Keys (Primary keys to identify…
Database Design and Implementation
Relational data model
Most new database installations are based on the relational data model
Logical structure is made up of a collection of relations
A relation, in mathematical set theory, is the definition of a table with columns (attributes) and rows (tuples)
A column in a relation will have a unique name within the table, a domain, and will have no "positional concepts"
A row in a relation will have only one value at the intersection of a column and row, uniqueness, a primary key, and no positional concepts
Types of tables
Base tables - relations that are actually stored in the database
Virtual tables - only exist in main memory
Primary Keys
A unique identifier that makes it possible to identify every row in a table
A primary key cannot contain the value null
Primary keys to identify people
A primary key should be some value that is highly unlikely ever to be null
A primary key value should never change
A primary key should avoid using meaningful data
Concatenated primary keys
A concatenated primary key should be made up of the smallest number of columns
Whenever possible the columns used in a concatenated primary key should be meaningless identifiers
Representing data relationships
Relational DBMS uses the relationships indicated by matching data between primary and foreign keys
Referential integrity
Referential integrity constraints are stored in the database, and enforced automatically by DBMS
Concatenated Foreign keys
Foreign keys do not always need to be made up of a single column
Some times there may be a concatenated foreign key that references a concatenated primary key
Views
People who develop database schema have the knowledge of and access to the entire schema, including direct access to the databases tables
View mechanism
A view is not stored with data
A view is stored under a name in a database table with a database query that will retrieve its data
Data dictionary
Structure of a relational database is stored in the databases data dictionary
A data dictionary is made up of a set of relations, identical in properties to the relations used to hold data
Types of information in a data dictionary
Definitions of the columns that make up each table
Integrity constraints placed on relations
Security information
Definitions of other database structure elements such as views and user defined domains