Assignment Due : May, 27Prior to beginning work on this interactive assignment, read Chapter 4 of your textbook and the Fundamentals of Relational Database Design (Links to an external site.), Entity Relationship Diagram (Links to an external site.), Ten Common Database Design Mistakes (Links to an external site.), and 10 Best Practices in Database Schema Design (Links to an external site.) online resources, and review any relevant information in this week’s lecture.Carefully review the instructions for the Week Six Database Design Project assignment including the ISM641 Database Design Project: Business Rules document https://ashford.instructure.com/courses/85356/files/15589233/download?download_frd=1downloadthat includes the entities and attributes information. This will become part of your final assignment in the course.Analyze the user needs as laid out in the Business Rules document https://ashford.instructure.com/courses/85356/files/15589233/download?download_frd=1download. Access your Virtual Lab and use Visio to create an entity-relationship diagram (ERD) from the entities and attributes listed for the ConstructCo Database Project. Your ERD must include the following.Identify all entities and attributesDefine all relationships between entities (including proper relation semantics)Select the appropriate data types for each of the attributesIdentify your primary keysOnce you have created your Visio, save it as a PDF and download to your computer from Practice Labs. Attach your ERD PDF to your initial post. In your written initial post explain the process you have selected in order to build the ERD and any challenges you might have experienced. List any tips that might be helpful to anyone planning to build an ERD. Discuss how you selected the different data types for your attributes and what considerations to take into account when selecting data types. Describe how you selected your primary keys for each of the tables and whether other attributes could have been selected as primary keys. Finally, review the benefits of using a database-agnostic tool like MS Visio versus the ERD building tools already available with most Relational Database Management System (RDBMS).Weekly Lecture: Week Two LectureAs discussed in the previous lecture, relational databases (DBs) are the most common DB engines in use today and probably will continue to be in the foreseeable future. Relational DBs are comprised of tables and the relationships between them. In order to understand what a DB system is, you must understand its structure. The DB system forces a structure on any set of data using its DB schema or DB model. A few different types of DB models in use today, but the most common is the Relational Database Model. Just about every DB program you can think of falls into the category of a Relational Database application.All data in a Relational Database is described in terms of tables and the relationship between tables. The data stored in the tables of a Relational Database can be stored and retrieved, efficiently and consistently using Structured Query Language (SQL). SQL is very ‘English’-like as it uses familiar words that make sense such as ‘GET’ and ‘SORT’ for the logical functions these words imply. It is fairly easy to use in its basic form for both stand-alone applications and web-based applications. SQL is used in all the common relational DB programs including Microsoft Access, Microsoft SQL Server, MySQL, DB2 and Oracle. During this course, we will use Microsoft SQL Server.A ‘data model’ defines and graphically depicts the data structure and relationships among the data elements. Two common modes currently in use are:The Entity-Relationship Model (ERD) — define how entities are associated with each other.The Semantic Object Model — used to interpret user data requirements and to graphically depict those requirements.When we perform data modeling, we must first determine the users’ needs and end goals. We identify the reports and forms that will be needed and then we identify specific data and the data structure. We will also need to keep in mind other components that will play an important role in the development of a DB. The following lists some of these components:Metadata: Metadata is the information about the actual data and is stored in the catalogs or directories describing the model.Structured Query Language: a query language. A query language is needed to access the data in a relational DBMS. When we design our DBs we must also consider some aspects with file storage. Space will be needed for data, metadata, and indexes.All data on a computer is stored in what are called ‘flat’ files. Flat files are unstructured data. Examples of common flat files are ASCII text or comma delimited files (.txt, .csv, etc.). There are also fixed length flat files where the rows of data are all of a specified length. In DB terms either kind of flat file consists of records, each with an identical format. If the records are stored sequentially by date or account number, we call that a sequential file. To access or change data, the sequential file must be read from the beginning of the file to find a specific record. Depending on the record operation (insert, modify, or delete), we may have to rewrite the whole file all over again with the new or modified data. This can be terribly inefficient and consume much in the way of overhead resources.An improvement both in efficiency and resource management consists of creating a key value for each record. We can then store the information in any order, but there would be a separate, sorted list of these key values. In order to insert, modify, or delete a record all we will have to do then is write the new record and the list of sorted keys if the key sort has changed. Another option is to store the data in a “linked list” which is a data structure where each record has a “pointer” to the start of the next record in the list. Records can be added anywhere and just the preceding link would need to be changed. This is a direct way of going from record to record on the physical disk. There are several record structures that can be defined in a database briefly described in the following list:The tree relationship is a form that has only one-to-many-relationships.The simple network relationship pattern data structure is one where the elements may have more than one parent as long as the parents are of different types.The complex network relationship has at least one many-to-many relationship.Tables as RelationsA table can be defined as a relation. For example, there are underlying domains, or possible values the fields may contain. Each field has its own domain. Some fields can potentially have an infinite domain, meaning there is no limit on what it may contain within the data type. Each column in the table corresponds to a field and therefore a certain domain no matter how small or how large. The table’s heading represents the relation’s heading, column, or field. Each row in the table represents a tuple or record. There are also different types of relations depending on relational algebra, operators, or other functions. For example you can have the following types of relations:namedbasederivedexpressiblequery resultintermediate resultview (virtual)snapshot (real)storedEach type has specific properties that can be used to perform certain functions. And each has its strengths and weaknesses, so it is important to understand them when you’re developing your database. More detailed information about the relation types can be found in your text and reading materials.Records, Rows, TuplesEach row in a relation, called a tuple or record, contains data about a specific occurrence of the type of entity represented by that table.Columns, Attributes, FieldsEach column in a table must describe a characteristic of the object identified in the table. Other name for column is field or attribute.Primary Keys, Foreign Keys, Integrity RulesRecords are identified by select fields called keys. A primary key is a field or combination of fields that uniquely identifies a record (row) in the table. Each table should have one primary key. A field in one table that serves as the primary key in another table is called a foreign key. Foreign key relationships to a primary key in a separate table allow data in tables to be merged so that query information can be extracted from the database. There are two types of integrity rules to do with the keys:The entity integrity rule states that the value of the primary key can never be a null value. It must always exist. Because a primary key is used to identify a unique row in a relational table, its value must always be specified and should never be unknown.The referential integrity rule states that if a relational table has a foreign key, then every value of the foreign key must either be null or match the values in the relational table in which that foreign key is a primary key.For more details about the concepts addressed in the lecture, be sure you review the chapters in your text. In our next lecture, we will review the normalization process and its role in resolving data anomalies.