How do you design a relational data model?

Purpose: Organizing content items in our CMS Scope: Slide Library Requirements: Up to date content, easily organized for sales use Stakeholders: Sales, RMs, PMs

purpose of DBMS Data redundancy and inconsistency. Difficult in accessing data. Data isolation. Data integrity. Concurrency is not possible. Security Problems. SCOPE: A DBMS is a very complex software system that consists of many components, or modules, including modules for implementing the catalog, query language processing, interface processing, accessing and buffering data, controlling concurrency, and handling data recovery and security.

Entities: Slide Name Attributes: Slide Owner(s), Date Uploaded, Expiration Date, Tags, Assigned List, Assigned Spot, Archive Status

Entities Entities represent objects or things in the real world that have a distinct existence. In the context of a database, an entity is typically something that you want to store information about. Entities are often represented as tables in a relational database. ex: Product Customer Employee Order Attributes Attributes are the properties or characteristics of an entity. They describe the entity and provide additional details about it. In a relational database, attributes are typically the columns of a table. Examples CustomerID: A unique identifier for the customer. Name: The name of the customer. Email: The email address of the customer. Phone: The phone number of the customer. Address: The physical address of the customer.

NOT NULL: This constraint tells that we cannot store a null value in a column. That is, if a column is specified as NOT NULL then we will not be able to store null in this particular column any more. UNIQUE: This constraint when specified with a column, tells that all the values in the column must be unique. That is, the values in any row of a column must not be repeated. PRIMARY KEY: A primary key is a field which can uniquely identify each row in a table. And this constraint is used to specify a field in a table as primary key. FOREIGN KEY: A Foreign key is a field which can uniquely identify each row in a another table. And this constraint is used to specify a field as Foreign key.

Zero, one, or more - Signified by a blank space. Each parent in the relationship is connected to zero, one, or more instances of the child entity or table. One or more - Signified by the letter P. Each parent in the relationship is connected to one or more instances of the child entity or table. Zero or one - Signified by the letter Z. Each parent in the relationship is connected to zero or one instance of the child entity or table. Exactly - Each parent in the relationship is connected to an exact number of instances of the child entity or table. You specify the number of instances. Range - Specify a range using the double dot notation.

Normalization is the process to eliminate data redundancy and enhance data integrity in the table. Normalization also helps to organize the data in the database. It is a multi-step process that sets the data into tabular form and removes the duplicated data from the relational tables. Normalization organizes the columns and tables of a database to ensure that database integrity constraints properly execute their dependencies. It is a systematic technique of decomposing tables to eliminate data redundancy (repetition) and undesirable characteristics like Insertion, Update, and Deletion anomalies.

Normalization should be considered with every step in relational database design. You cannot properly define entities (tables), relationships or primary/foreign keys without the process of normalization. In fact, there second and third forms of normalization deal directly with ensuring the proper relationship between the fields in the data and the primary key (composite or singular). I'll say it again for emphasis: Every step in designing a relational database should be done through the lens of normalization.