Unit 21 – M2
A - Explain domain integrity constraints
1. Domain integrity constraints in general
Domain integrity helps database developers determine what values are valid for a particular column. The way that this process is enforced is by using standard procedures and rules during the database design phase. A domain definition can be used instead of data types in a column definition. This has the advantage that the same definition off data type, check ‘clauses’ and ‘default’ values can be used in many column definition and therefore those columns are guaranteed to have the same attributes. When functions are performed on the data the functions must ensure integrity. Examples of function are transforming the data, storing the history,
…show more content…
2. List-type constraints (lookups from your Unit 18-P4), what is their use, what effect do they have on a database (include a screenshot of a lookup from your own database)
• Check constraint
A check constraint is a rule the identifies acceptable column values for data in a row within a SQL server table. Checks constraints help enforce domain integrity. Domain integrity produces an error message and the entire insert or updates operation will fail.
• Foreign key constraint
Foreign key constraints can be used to define required relationships between and with tables. When a foreign key constraint is added to an existing column or columns in the tables SQL server, by default the process will check the existing data in the column to ensure that all values, except NULL, exist in the column(s) of the referenced primary key or unique constraint.
• Not null constraint
A not-null constraint is a restriction placed on a column in a relational database table. It enforces the condition that, in that column, every row of the data must contain a value – it cannot be left blank during insert or updates operation will fail.
3. Format-type constraints, what is their use, what effect do they have on the database (include a screenshot of a lookup from your own database)
From this screenshot below, we are able to understand that user can encounter the issue of formatting
* As explained throughout this course, entity relationship modeling is a critical element of database design. If the database is not properly modeled, it is unlikely that the database will be properly developed. Using this knowledge, explain the key reasons why entity relationship modeling is important, and determine at least (1) way in which it impacts the overall development of the database.
# 6: Given a physical database containing tables and relationships and business requirements, create the necessary queries.
A relational database is a database that consists of a collection of tables with columns showing entities, and rows showing data. This type of database uses a primary key and foreign key. The foreign key in another table will point to the primary key of a table, and this is how tables can relate to each other. This permits for one-to-one, one-to-many, and many-to-many relationship between the data. An advantage of relational databases includes the ease of adding or modifying new tables and entities without needing to change the structure of the database already in place. Relational database have many features, including indexing, setting data type, and setting validation tests, all these help to ensure data integrity.
Read the textbook. Do research on databases. All answers should be analytical, using IGEA and MLA. Please use own words/original thought, not regurgitation of facts. This is the expectation all year.
If we consider an example of a database for billing the claims to the provider, we have two tables in the database as ‘Claims’ and ‘Provider’. ‘Claims’ have the claim information such as (ClaimNumber, ClaimType, Type of service, Admission details) and ‘Provider’ have the provider information such as (ProviderID, FederalTaxID, NPI, PointOfService). The primary keys for ‘Claims’ is “ClaimNumber” and for ‘Provider’ is “ProviderID”.
P4- Outline the benefits of having a variety of data types available to the programmer
There will be many different constraints in the database. An example of a constraint is one if the students' GPA will have to be between 0.00 and 4.00. Another example would be that each class can only have one instructor. There would be a constraint on the social security number ensuring that there are only nine numbers and in the format is xxx-xx-xxxx, and each individual number would be between 0 and 9. There would be a constraint for the telephone numbers as well. Each individual number would be between 0 and 9 in the format of (xxx)-xxx-xxxx.
First our team will talk about how Microsoft Access was used to create these tables by Huffman Trucking Database. Then how we used the tables with made up information to be tested on. Then on how we used forms in the maintenance database system. While the creation of relationships and normalization is explained in the process of using queries to search the database (University of Phoenix, 2007).
Another difficulty using relations that have redundant information is the problem of update anomalies. These can be classified as Insertion, Deletion or Modification anamolies.
Although the DBMS products reviewed here can rightfully claim to be relational, their support of the relational data model must be scrutinized closely. Obviously they all support the basic relational concepts, such as data stored in tables and accessed b y high-level set operations, mostly through SQL. However, not one of the Big Six supports domains. It is crucial that these products address this fundamental concept of the relational data model. You must be able to define your domains and then specify y our table columns, and preferably also your stored procedure variables and parameters, in terms of the domains. This process is necessary to ensure tight type-checking, as you can do in some programming languages. If a "small" (in terms of market share) product such as Interbase could implement domains five years ago, surely the Big Six should be able to "get it right" today.
Provides UML modelling for database designs, with the ability to represent the integration of data and application requirements through logical and physical designs
ACID properties enable the system to manage a certain characteristic for each transaction. Every transaction is assured that these characteristics are in place. Therefore, SQL can also guarantee the stability, security and predictability both of the entire database and every particular transaction.
Integrity check is achieved by lookup that uses templates to access the dimension tables in the target data mart.
Also, DBMS can provide consistency rules that the database is not permitted to violate to maintain database integrity which refers to