Database Systems with SQL - Full Course - Database Systems - Database Design: Implementing Entities, Relationships and Attributes

In the logical design phase, each of the entities becomes an independent table and each attribute becomes a column. Then the table and columns are reviewed and revised in subsequent logical design steps. Primary keys should be: Unique and NOT NULL Stable – not change in value Simple – easy to enter and use Meaningless – should not store sensitive, or descriptive data, because that may change It’s best to try to use a single column primary ID, but if none such exists, then use a composite primary key, which is made up of 2 or more columns. A better alternative is to create a single column artificial key such as ID, and having a unique numbered identifier such as 001, 002, 003, and so on. These are usually generated automatically by the database and auto-incremented when a new row is inserted, which makes them easy to deal with. A subtype entity becomes a subtype table and its primary key is is a foreign key to the supertypes primary key. The foreign key implements the 'IsA' dependency relationship. Foreign keys that implement dependency relationships usually have the following referential integrity actions: Cascade on primary key update and delete A dependent entity becomes a dependent table. The primary key is typically composite which includes a foreign key that references the master table primary key. If there’s not suitable column when creating the composite primary key, you can create and use an artificial column. Implement relationships means converting relationships into tables or keys, based on relationship cardinality. Your 1-1 cardinality relationship becomes a foreign key which can go in the table on either side of the relationship. The foreign key is typically put in the table with fewer rows, in order to minimize the number of NULL values. The foreign key refers to the primary key on the opposite side of the relationship. Your M-1 or 1-M cardinality relationship becomes a foreign key on the many (M) side, referring to the table on the one (1) side. Your M-M relationship becomes a new table that contains two foreign keys, referring to the primary keys of each related table. Attributes can be either singular or plural: Each entity instance has at most one singular attribute instance, or can have many plural attribute instances. Singular attributes stay in same table, while plural attributes move to a new table. The new tables primary key should be a composite key made up of the plural attribute and the foreign key, which references the initial table. Name this new table with the initial table’s name, followed by the name of the attribute. Attributes can be: UNIQUE, which have maximum cardinality of one NOT NULL, which have minimum cardinality of one PRIMARY KEY, which have a minimum and maximum cardinality of one If no keyword is specified, then it has a minimum cardinality of zero, and NULL values are allowed. Subscribe to Appficial for more programming videos coming soon. Also, don't forget to click LIKE and comment on the video if it helped you out!