Database Construction and Management
What Is a database? A database Is one or more large structured sets of persistent data, usually associated with software (database management system) to update and query the data. A simple database might be a single file containing many records, each of which contains the same set of fields where each field is a certain fixed width ; From FOLD A database is a collection of data elements (facts) stored in a computer in a systematic way, such that a computer program can consult it to answer questions.
The answers to those questions become information that can be used to make sections that may not be made with the data elements alone. The computer program used to manage and query a database is known as a database management system (DB’S). A database is an organized collection of data used for the purpose of modeling some type of organization or organizational process. It really doesn’t matter whether you’re using paper or a computer software program to collect and store the data.
As long as you’re gathering data In some organized manner for a specific purpose, you’ve got a database. Database management systems range from the extremely simple to the highly complex. Differences among Debases include whether they are capable of ensuring the integrity of the data; whether they may be used by many users at once; and what sorts of conclusions they can be programmed to compute from a set of data. 2. Applications of databases Databases are used in many applications, spanning virtually the entire range of computer software.
Databases are the preferred method of storage for large multiuse applications, where coordination between many users is needed. Even individual users find them convenient, and many electronic mail programs and personal organizers are based on standard database technology. Software database drivers are available for most database platforms so that application software can use a common application programming interface (API) to retrieve the information stored in a database. One example of a database driver API is JDBC. . Database Models A database model is essentially the architecture that the DB’S uses to store objects within the database and relate them to one another. 3. 1 . The Relational Database Model The relational database was first conceived in 1969 and has arguably become the most widely used database model in database management today. The father of the relational model, Dry. Edgar F. Cod, was an IBM research scientist in the late sass and was at that time looking into new ways to handle large amounts of data.
HIS dissatisfaction with the database models and database products of the time led him to begin thinking of ways to apply the dullness’s and structures of mathematics to solve the myriad of problems he had been encountering. Being a mathematician by mathematics to solve problems, such as data redundancy, weak data integrity, and a database structure’s overconfidence on its physical implementation. Dry. Cod formally presented his new relational model in a landmark work entitled “A Relational Model of Data for Large Shared Databanks” in June of 1970.
He based his new model on two branches of mathematics-?set theory and first-order predicate logic. Indeed, the name of the model itself is derived from the term relation, which is part of set theory. (A widely held misconception is that the relational model derives its name from the fact that tables within a relational database can be related to one another. ) Page 1 of 14 Lecturer: Austin Own (www. Astounding. Com) A relational database stores data in relations, which the user perceives as tables. Each relation is composed of tepees, or records, and attributes, or fields.
The physical order of the records or fields in a table is completely immaterial, and each record in the table is identified by a field that contains a unique value. These are the two characteristics of a relational database that allow the data to exist independently of the way it is physically stored in the computer. As such, a user isn’t required to know the physical location of a record in order to retrieve its data. 4. Conceptual Database Design Components Conceptual database design involves studying and modeling the data in a confidentiality’s manner.
Data modeling is an abstraction activity in that the details of the values of individual data observations are ignored in favor of the structure, relationships, names and formats of the data of interest, although a list of valid values is frequently recorded. The activity strives to bring the data structures of interest together in a cohesive, inseparable, whole by eliminating unnecessary data redundancies and relating data structures by relationships. Several techniques have been developed for the design of a data models. One among the most noticeable Entity-relationship diagrams. . 1 .
Entities An entity is a person, place, thing, event, or concept about which data is collected. In other words, entities are the real world things in which we have sufficient interest to capture and store data about in a database. An entity is represented as a rectangle on the diagram. Just about anything that can be named with a noun can be an entity. However, to avoid designing everything on the planet into our database, we restrict ourselves to entities of interest to the people who will use our database. Each entity shown in the conceptual model represents the entire class for that entity.
For example, the Customer entity represents the collection of all KEEP customers. The individual customers are called instances of the entity. 4. 2. Attributes An attribute is a unit fact that characterizes or describes an entity in some way. These are represented on the conceptual design diagram as names inside the rectangle that represents the entity to which they belong. The attribute (or attributes) identifier for the entity. A unique identifier, as the name suggests, provides a unique value for each instance of the entity.
A unique identifier can be composed of multiple attributes, but when this happens, it is still considered Just one unique identifier. We say attributes are a unit fact because they should be atomic, meaning they cannot be broken down into smaller units in any meaningful way. An attribute is therefore the smallest named unit of data that appears in a database system. In this sense, Address should be considered a suspect entity because it could easily be broken down into Addressing and Addressing, as is commonly done in business systems.
This change would add meaning because it makes it easier to print address labels, for example. On the other hand, database design is not an exact science, and judgment calls must be made. Although it is possible to break the Contact Name attribute into component attributes, such as First Name, Middle Initial, and Last Name, we must ask ourselves whether such a change adds meaning or value. There is no right or wrong answer here, so we must rely on the people who will be using the database, or perhaps those who are funding the database project, to help us with such decisions.
Always remember that an attribute must describe or characterize the entity in some way (for example, size, shape, color, quantity, location). Page 2 of 14 4. 3. Relationships Relationships are the associations among the entities. Because databases are all about storing related data, the relationships become the glue that holds the database together. Relationships are shown on the conceptual design diagram as lines connecting one or more entities.
Each end of a relationship line shows the maximum cardinality of the relationship, which is the maximum number of instances of one entity that can be associated with the entity on the opposite end of the line. The maximum cardinality may be one (where the line has no special symbol on its ND) or many (where the line has a crow’s foot on the end). Just short of the end of the line is another symbol that shows the minimum cardinality, which is the minimum number of instances of one entity that can be associated with the entity on the opposite end of the line.
The minimum cardinality may be zero, denoted with a circle drawn on the line, or one, denoted with a short vertical line or tick mark drawn across the relationship line. Many data meddlers use two vertical lines to mean “one and only one. ” Learning to read relationships takes practice, and learning to define and draw them correctly takes a lot of practice. The trick is to think about the association between the entities in one direction, and then reverse your perspective to think about it in the opposite direction.
For the relationship between Customer and Order, for example, we must ask two questions: “Each customer can have how many orders? ” followed by “Each order can have how many customers? ” Relationships may thus be in the following sections. Some people will say many-to-one is also a relationship type, but in reality, it is only a one-to-many relationship looked at with a reverse perspective. Relationship types are best learned by example. Getting the legislations right is essential to a successful design. 4. 4. Terminology Some basic terminology applies to relationships.
The entities that are related are called participants, and the number of participants in a relationship is its degree. The vast majority of relationships are binary, having two participants, but unary relationships (a relation that is related to itself) are also common, while ternary relationships (with three participants) are not unknown. The participation of an entity in a relationship can be classified as total participation or partial participation, depending on whether the entity can exist without articulating in the relationship.
For example, given the two entities Customer and Order, the participation of Customer in the relationship is partial since Customer details might be entered before the customer has placed any orders. Order, on the other hand, has total participation, since an order can’t be placed except by a Customer. The same principle is sometimes used to classify the entities themselves as either weak (having total participation) or regular (having partial participation). Weak entities can exist only in relationships with other entities, while regular entities can exist in isolation.
This classification is part of the Entity Relationship (E/R) diagramming method as originally described by Chem.. You can use the notation shown in the figure below in EIRE diagrams to show whether an entity is weak or regular. A crows foot is used to describe the “many’ side of a one-to-many or many-to-many relationship. A crows foot looks quite literally like the imprint of a crows foot in some mud, with three splayed “toes. ” Page 3 of 14 The classification of participation in a relationship is also an indication of the optionally of the relationship: whether or not an entity is required to participate in a even relationship.
This is a rather tricky area because, often, implementation by the database engines does not match the problem domain. The maximum number of instances of one entity that can be associated with an instance of another entity is what we refer to as the cardinality of a relationship. There are three generic flavors of cardinality: one-to-one, one-to-many, and many- cardinality and optionally of relationships. Alternative techniques are available, and you must use the one that works best for you. 5. Understanding the Basics of Tables In data model theory, a table is a bucket into which data is poured.
Records are repeated over and over again in the vertical dimension, duplicating field structures from the horizontal dimension. A table is effectively a structure containing fields across it in one dimension defining the structure of records repeatedly added to that table – a record is an instance of the table it belongs to. In other words, all records in the same tables have the same field structure applied to them. 2 Note that both degree and cardinality have slightly different meanings when applied to relationships than when applied to relations Page 4 of 14 5. . Records, Rows, and Tepees The terms record, row, and duple all mean the same thing. They are terms used to describe a record in a table. The structure of fields is applied to each record entry in a table. There is really nothing to understand other than that a table can have multiple fields, whereas that set of fields can have many records created in that table, and data can subsequently be accessed according to the field structure of the table, record by record. 5. 2. Fields, Columns and Attributes The terms field, column, and attribute all mean the same thing.
They are all terms used to describe a field in a table. A field applies structure and definition to a chunk of data within each repeated record. Data is not actually repeated on every record, but the structure of fields is applied to each record. So, data on each record can be different, both for the record as a whole, and for each field value. Note the use of the term “can be” rather than “is,” implying that there can be duplication across both fields and records, depending on requirements and constraints. A constraint constrains (restricts) a value. 6.
Keys Keys are crucial to a table structure for the following reasons: They ensure that each cord in a table is precisely identified. As you already know, a table represents a singular collection of similar objects or events. (For example, a CLASSES table represents a collection of classes, not Just a single class. ) The complete set of records within the table constitutes the collection, and each record represents a unique instance of the table’s subject within that collection. You must have some means of accurately identifying each instance, and a key is the device that allows you to do so. Impotent of table-level integrity and relationship-level integrity. For instance, they enable you to ensure that a table has unique records and that the fields you use to establish a relationship between a pair of tables always contain matching values. They serve to establish table relationships. You use keys to establish a relationship between a pair of tables. Always make certain that you define the appropriate keys for each table. Doing so will help you guarantee that the table structures are sound, that redundant data within each table is minimal, and that the relationships between tables are solid.
There are three types of keys: candidate, primary, and foreign keys. A key’s type determines its unction within the table. Keys) 6. 1 . Candidate Keys (Unique Keys) The first type of key you establish for a table is the candidate key, which is a field or set of fields that uniquely identifies a single instance of the table’s subject. Each table must have at least one candidate key. You’ll eventually examine the table’s pool of available candidate keys and designate one of them as the official primary key for the table. 6. 1 . 1.
Elements off Candidate Key Before you can designate a field as a candidate key, you must make certain it complies with all of the Elements of a Candidate Key. These elements constitute a set f guidelines you can use to determine whether the field is fit to serve as a candidate key. You cannot designate a field as a candidate key if it fails to conform to any of these elements. It cannot be a multipart field. You’ve seen the problems with multipart fields, so you know that using one as an identifier is a bad idea. Page 5 of 14 It must contain unique values.
This element helps you guard against duplicating a given record within the table. Duplicate records are Just as bad as duplicate fields, and you must avoid them at all costs. It cannot contain null values. As you already know, a null value represents the absence of a value. There’s absolutely no way a candidate key field can identify a given record if its value is null. Its value cannot cause a breach of the organization’s security or privacy rules. Values such as passwords and Social Security Numbers are not suitable for use as a candidate key. Its value is not optional in whole or in part.
A value that is optional implies that it may violates the previous element and is, therefore, unacceptable. (This caveat is especially applicable when you want to use two or more fields as a candidate key. ) It comprises a minimum number of fields necessary to define uniqueness. You can use combination of fields (treated as a single unit) to serve as a candidate key, so long as each field contributes to defining a unique value. Try to use as few fields as possible, however, because overly complex candidate keys can ultimately prove to be difficult to work with and difficult to understand.
Its values must uniquely and exclusively identify each record in the table. This element helps you guard against duplicate records and ensures that you can accurately reference any of the table’s records from other tables in the database. Its value must exclusively identify the value of each field within a given record. This element ensures that the table’s candidate keys provide the only means of identifying each field value within the record. (You’ll learn more about this particular element in the section on primary keys. ) Its value can be modified only in rare or extreme cases.
You should never change the value of a candidate key unless you have an absolute and compelling reason to do so. A field is likely to have difficulty conforming to the previous elements if you can change its value arbitrarily. Establishing a candidate key for a table is quite simple: Look for a field or set of fields that conforms to all of the Elements of a Candidate Key. You’ll probably be able to define more than one candidate key for a given table. Loading a table with sample data will give you the means to identify potential candidate keys accurately. Each table must have at least one candidate key. 6. 1. 2.
Artificial Candidate Keys When you determine that a table does not contain a candidate key, you can create and use an artificial (or surrogate) candidate key. (It’s artificial in the sense that it didn’t occur “naturally” in the table; you have to manufacture it. ) You establish an artificial candidate key by creating a new field that conforms to all of the Elements of Candidate Key and then adding it to the table; this field becomes the official candidate key. You may also choose to create an artificial candidate key when it would be a stronger (and thus, more appropriate) candidate key than any of the existing candidate keys. . 1. 3. Primary Keys Once you’ve established all the candidate keys that seem appropriate for every table, your next task is to establish a primary key for each table, which is the most important key of all. A primary key field exclusively identifies the table throughout the database structure and helps establish relationships with other tables. Page 6 of 14 A primary key value uniquely identifies a given record within a table and exclusively represents that record throughout the entire database. It also helps to guard against duplicate records. A primary key must conform to the exact same elements as a candidate key.
This requirement is easy to fulfill because you select a primary key from a table’s pool of available candidate keys. The process of selecting a primary key is somewhat similar to that of a presidential election. Every four years, several people run for the office of president of the United States. These individuals are known as “candidates” and they have all of the qualifications required to become president. A national election is held, and a single individual from the pool of available presidential candidates is elected to serve as the country’s official president.
Similarly, you identify each qualified candidate key in the table, run your own election, and select one of them to become the official primary key of the table. You’ve already identified the candidates, so now it’s election time! Assuming that there is no other marginal preference, here are a couple of guidelines you can use to select an appropriate primary key: If you have a simple (single-field) candidate key and a composite candidate key, choose the simple candidate key. It’s always best to use a candidate key that contains the least number of fields.
Choose a candidate key that incorporates part of the table name within its own name. For example, a candidate key with a name such as SALES INVOICE NUMBER is a good choice for the SALES INVOICES table. Examine the candidate keys and choose one to serve as the primary key for the table. The choice is largely arbitrary-?you can choose the one that you believe most accurately identifies the table’s subject or the one that is the most meaningful to everyone in the organization. 6. 1. 4. Elements off Primary Key The candidate key you ultimately choose becomes the primary key of the table and is governed by the Elements of a Primary Key.
These elements are exactly the same as those for the candidate key, and you should enforce them to the letter. For the sake of clarity, here are the Elements off Primary Key: It cannot be a multipart field. It must contain unique values. It cannot contain null values. Its value cannot cause a breach of the organization’s security or privacy rules. Its value is not optional in whole or in part. It comprises a minimum number of fields necessary to define uniqueness. Its values exclusively identify the value of each field within a given record.
Its value can be modified only in rare or extreme cases. Before you finalize your selection of a primary key, it is imperative that you make absolutely certain that the primary key fully complies with this particular element: Its value must exclusively identify the value of each field within a given record. Each field value in a given record should be unique throughout the entire database (unless it is participating in establishing a relationship between a pair of tables) and would have only one exclusive means of identification-?the specific primary key value for that record.
Page 7 of 14 You can determine whether a primary key fully complies with this element by following these steps: Load the table with sample data. Select a record for test purposes and note the current primary key value. Examine the value of the first field (the one immediately after the primary key) and ask yourself this question: Does this primary key value exclusively identify the current value of? If the answer is yes, move to the next field and repeat the question otherwise (e if the answer is no), move the field from the table, move to the next field and repeat the question.
Continue this procedure until you’ve examined every field value in the record. A field value that the primary key does not exclusively identify indicates that the field itself is unnecessary to the table’s structure; therefore, you should remove the field and reconfirm that the table complies with the Elements of the Ideal Table (see below). You can then add the field you Just removed to another table structure, if appropriate, or you can discard it completely because it is truly unnecessary. 6. 2.
Elements of the Ideal Table You can identify a potentially problematic table structure by determining whether it complies with the Elements of the Ideal Table. These elements constitute a set of guidelines you can use to create sound table structures and to spot poorly designed tables easily. It represents a single subject, which can be an object or event. As long as you guarantee that each of your tables represents a single subject, you greatly reduce the risk of potential data-integrity problems.
It has a primary key. This is important for two reasons: It uniquely identifies each cord within a table, and it plays a key role in establishing table relationships. Additionally, it has specific characteristics that help to implement and enforce will eventually have data-integrity problems. It does not contain multipart or multivolume fields. It does not contain calculated fields. It does not contain unnecessary duplicate fields. Note that this guideline does not apply to fields used to relate a set of tables together One of the hallmarks of a poorly designed table is the inclusion of duplicate fields from other tables. You might feel compelled to add duplicate fields to a table for one of two reasons: to provide preference information or to indicate multiple occurrences of a particular type of value. Duplicate fields such as these raise various difficulties when you work with the data or attempt to retrieve information from the table.
It contains only an absolute minimum amount of redundant data. Remember that a relational database will never be completely free of redundant data. But you can-? and should-?make certain that each table contains as little redundant data as possible. 6. 2. 1. Rules for Establishing a Primary Key As you create a primary key for each table in the database, keep these two rules in mind: Each table must have one-?and only one-?primary key. Because the primary key must conform to each of the elements that govern it, only one primary key is necessary for a particular table.
Each primary key within the database must be unique-?no two tables should have the same primary key unless one of them is a subset table. The primary key exclusively identifies a table throughout the database structure; therefore, each table Page 8 of 14 must have its own unique primary key in order to avoid any possible confusion or ambiguity concerning the table’s identity. A subset table is excluded from this rule cause it represents a more specific version of a particular data table’s subject-? both tables must share the same primary key. . 2. 2. Alternate Keys Once you’ve selected a candidate key to serve as the primary key for a particular table, the rest of the candidate keys are known as as alternate keys.
Get access to
Guarantee No Hidden