Web Hosting Java Blog

Chapter 2 DATABASE DESIGN FOR SQL SERVER 2000 71 cation and are therefore separated away in a separate entity. A one- to-one relationship is usually drawn on an ER diagram as a line with both ends having dots. One-to-Many One-to-many relationships exist when a single instance of an entity (the parent entity) relates to many instances of another entity (the child entity). One-to-many relationships are the most common relationships in the real world. For example, a customer may have many orders, and a manufactured product may have many components. A one-to-many relationship is usually drawn on an ER diagram as a line with the child end having a dot. The child end is a crow s foot in Chen and IE data models. Many-to-Many This type of relationship occurs when many rows or data elements in an entity are associated with many rows or data elements in another entity. For example, a many-to-many relationship occurs between the Trainee and Course entities. Many Trainees can enroll in a single course, and one trainee can be enrolled in numerous courses. This type of relationship is not uncommon in the real world. However, SQL Server doesn t actually directly implement many-to-many relationships. A many-to-many relationship is implemented using three entities. The two main entities are connected together using a third entity. The third entity contains keys and interrelationship information. Each entity is connected to the new entity as a one-to-many relationship. To discover the cardinality of a relationship, you look at the correlation between the entities. For example, a Trainee may take many Courses, and a Course may have many Trainees. The problem isn t SQL Server not implementing many-to-many relationships, but that relational databases in general cannot directly support this kind of relationship. Therefore, it is a combination of two, one-to-many ties. Identifying Relationships Remember, entities are the nouns in the model; akin to this, relationships are the verbs. This verb/noun concept is a key factor in

For high quality website hosting services please check cheap web hosting website.

70 Part I EXAM PREPARATION or other unique data elements. Relationships define a real-world connection or link between one entity and another. In the modeling process, you attempt to discover how things and which things are related to one another. Within the business problem you are modeling, each object requires full definition, including how it is related to other objects. These relationships are usually defined as a numeric link connecting the entities together based on the number of data elements in one entity that are related to one or more full elements in another entity. It can be described as how many of one thing can relate to how many of something else. This property of a relationship is known as the cardinality of a relationship. In the Lloyd s Hospital example, patients buy or take drugs, so a Patient entity would probably be related to a Medicine entity. Relationships can be made from one entity to another in essentially three different ways. These different relations are one-to-one, one-tomany, and many-to-many. The relationships between two entities is often called a binary relationship. The following sections describe how to identify, apply, and choose the correct relationships between the different entities in your model. Although a binary relationship between two entities is common, there may be relationships between three entities (ternary), and more. Of course, an entity might be related to itself (unary or a self-referencing entity). One-To-One This type of relationship occurs when one row or data element of an entity is associated with only one row or element in the second entity. It is not surprising that one-to-one relationships are uncommon in the real world. They are used mostly when an entity has an extraordinarily large number of attributes, so the entity is split in two to make it more easy to manage, and applications perform better. An extra entity might be desired during the development of the physical storage locations of the data. By separating off seldom-used data from more frequently used information, faster data retrieval and updates can be accommodated. For example, employees may have pictures but the pictures are not often retrieved by a front-end appli

For reliable and cheap web hosting services please check cheap web hosting website.

Chapter 2 DATABASE DESIGN FOR SQL SERVER 2000 69 particular correlation to the data. Primary Keys and Foreign Keys are an important aspect of setting up relationships. Whereas a Primary Key defines a unique value for a row in the entity, a Foreign Key is used as a mechanism for one entity s records to be related to another entity. Foreign Keys Foreign Keys help in the relational process between two entities. When a Primary Key is created on a parent entity, it is connected to another entity by linking to the other entity s Foreign Key. For example, in an invoice situation, there are usually two entities: one for invoice general information and the other for invoice details. The invoice details would contain a hook onto the invoice general entity through the use of a Foreign Key potentially the invoice number or a surrogate key. Foreign Keys help in the relational process. They define a parent/child relationship between records in an entity. For instance, we have a title entity and a publisher entity. Titles are published by publishers, and we want to identify which titles are published by which publisher. Rows in the titles entity are related to rows in the publisher entity. One way we can document this relationship is, when creating a new title row, we store the publisherid value with the title. The publisherid column in the title entity will contain values only from the publisherid column in the publisher entity (this column is the Primary Key of publisher). When we take values from the Primary Key of the publisher entity (or any entity) and store those values in rows of a child entity (like title), the child column is a Foreign Key. Understanding Entity Relationships Relationships are the final component in an ER model, allowing for a logical linkage between one entity and another. A relationship in an ER model connects the data elements of two entities that contain information about the same real-world element. The primary entity in a relationship provides some of the data, and other entities provide further related data. A relationship definition states how two entities are connected. This connection when the physical structure is developed will become Foreign Key connections to Primary Keys TIPEXAM Data Modeling and MS Exams If you ever intend to sit the 70-100 exam, Analyzing Requirements and Defining Solution Architectures, data modeling plays an important role in the exam, and surrogate keys are recommended for all structures. This exam is the core exam for the MCSD certification.

For reliable and cheap web hosting services please check tomcat web hosting website.

Chapter 2 DATABASE DESIGN FOR SQL SERVER 2000 69 particular correlation to the data. Primary Keys and Foreign Keys are an important aspect of setting up relationships. Whereas a Primary Key defines a unique value for a row in the entity, a Foreign Key is used as a mechanism for one entity s records to be related to another entity. Foreign Keys Foreign Keys help in the relational process between two entities. When a Primary Key is created on a parent entity, it is connected to another entity by linking to the other entity s Foreign Key. For example, in an invoice situation, there are usually two entities: one for invoice general information and the other for invoice details. The invoice details would contain a hook onto the invoice general entity through the use of a Foreign Key potentially the invoice number or a surrogate key. Foreign Keys help in the relational process. They define a parent/child relationship between records in an entity. For instance, we have a title entity and a publisher entity. Titles are published by publishers, and we want to identify which titles are published by which publisher. Rows in the titles entity are related to rows in the publisher entity. One way we can document this relationship is, when creating a new title row, we store the publisherid value with the title. The publisherid column in the title entity will contain values only from the publisherid column in the publisher entity (this column is the Primary Key of publisher). When we take values from the Primary Key of the publisher entity (or any entity) and store those values in rows of a child entity (like title), the child column is a Foreign Key. Understanding Entity Relationships Relationships are the final component in an ER model, allowing for a logical linkage between one entity and another. A relationship in an ER model connects the data elements of two entities that contain information about the same real-world element. The primary entity in a relationship provides some of the data, and other entities provide further related data. A relationship definition states how two entities are connected. This connection when the physical structure is developed will become Foreign Key connections to Primary Keys TIPEXAM Data Modeling and MS Exams If you ever intend to sit the 70-100 exam, Analyzing Requirements and Defining Solution Architectures, data modeling plays an important role in the exam, and surrogate keys are recommended for all structures. This exam is the core exam for the MCSD certification.

For high quality website hosting services please check cheap web hosting website.

NOTE 68 Part I EXAM PREPARATION DESIGNING ENTITY KEYS . Design entity keys. Considerations include FOREIGN KEY constraints, PRIMARY KEY constraints, and UNIQUE constraints. Specify attributes that uniquely identify records. Specify attributes that reference other entities. Entity key definition is an important part of designing the structure of an ER model. Two types of keys exist: Primary and Foreign. These keys are used in establishing relationships and also provide an easier and more efficient way to retrieve data in the physical implementation. Primary Keys Primary Keys consist of sets of attributes whose values uniquely identify the rows in an entity. Primary Keys give an ID to a row. They make the row unique throughout the entity. This means that rows can easily located by this identifier. Defining Primary Keys Primary Keys Primary Keys can only be used for columns or attributes that don t can be implemented on columns in SQL Server 2000 in three ways. The allow null values. Allowing null values would mean that a row would first is to use the stored procedure not be uniquely identified. Also, the attribute chosen to hold a sp_primarykey. The second way is Primary Key must have values unique throughout the entity. to create a primary key constraint Choosing a Primary Key for a column is relatively simple in contrast on the column. Finally, you can create to choosing entities and attributes. Identifying Primary Keys is also a unique index on a Primary Key part of the Microsoft exam objective, so it s advisable that you have a candidate. good grasp of designing entity keys. A Primary Key should be a numeric value if possible, but this is not a hard and fast rule. To identify a Primary Key, a single investigation has to be taken: whether the column holds true when you ask whether this attribute can carry completely unique, known values throughout the entity. If the answer is a solid yes, no more investigation is needed; the column is worthy of being named a Primary Key column. However, if you notice that none of the attributes in the entity can uniquely identify themselves, it s possible to add an additional surrogate attribute. In many cases surrogate keys are actually a preferred mechanism because the values never need to change. Some designers dislike its use because it adds a column to the data structure that has no

For high quality website hosting services please check cheap web hosting website.

NOTE 68 Part I EXAM PREPARATION DESIGNING ENTITY KEYS . Design entity keys. Considerations include FOREIGN KEY constraints, PRIMARY KEY constraints, and UNIQUE constraints. Specify attributes that uniquely identify records. Specify attributes that reference other entities. Entity key definition is an important part of designing the structure of an ER model. Two types of keys exist: Primary and Foreign. These keys are used in establishing relationships and also provide an easier and more efficient way to retrieve data in the physical implementation. Primary Keys Primary Keys consist of sets of attributes whose values uniquely identify the rows in an entity. Primary Keys give an ID to a row. They make the row unique throughout the entity. This means that rows can easily located by this identifier. Defining Primary Keys Primary Keys Primary Keys can only be used for columns or attributes that don t can be implemented on columns in SQL Server 2000 in three ways. The allow null values. Allowing null values would mean that a row would first is to use the stored procedure not be uniquely identified. Also, the attribute chosen to hold a sp_primarykey. The second way is Primary Key must have values unique throughout the entity. to create a primary key constraint Choosing a Primary Key for a column is relatively simple in contrast on the column. Finally, you can create to choosing entities and attributes. Identifying Primary Keys is also a unique index on a Primary Key part of the Microsoft exam objective, so it s advisable that you have a candidate. good grasp of designing entity keys. A Primary Key should be a numeric value if possible, but this is not a hard and fast rule. To identify a Primary Key, a single investigation has to be taken: whether the column holds true when you ask whether this attribute can carry completely unique, known values throughout the entity. If the answer is a solid yes, no more investigation is needed; the column is worthy of being named a Primary Key column. However, if you notice that none of the attributes in the entity can uniquely identify themselves, it s possible to add an additional surrogate attribute. In many cases surrogate keys are actually a preferred mechanism because the values never need to change. Some designers dislike its use because it adds a column to the data structure that has no

For reliable and cheap web hosting services please check tomcat web hosting website.

Chapter 2 DATABASE DESIGN FOR SQL SERVER 2000 67 When attributes are decomposed, different methods of ensuring data integrity can be applied to the broken-down segments rather than the attribute as a whole. For example, a Zip attribute can be checked as an integer and City as a string. Secondly, decomposing aids in sorting and improving data retrieval performance. A generic attribute Address contains the street, city, state, and zip. To just get the state of a customer in Washington, you have to select the whole Address attribute and parse it to find Washington, thereby degrading performance because of the redundant data retrieved. If you just have four separate attributes, you can select the State column and get your results more quickly. This same rule applies to updating data. It s easier to update a single part of Address rather than parsing and then updating the whole Address attribute. In most cases, an attribute is identified for each entity that will act as a key. This attribute could be a customer number, product number, or simply an attribute to serve no other purpose but to act as a key identifier. When an attribute is added solely for the sake of being an identifier, it is known as a surrogate key. Whether you use a natural or surrogate key, that key will represent an important part in establishing relationships. The term Primary Key is assigned to this attribute. Even though SQL Server does not mandate the use of a Primary Key, one should be defined simply as a matter of good practice. General Recap on Data Modeling When designing a data model, you do not need to know how databases are actually implemented in SQL Server. In fact, the primary goal at this stage is picking the facts out of a given problem definition. A case scenario in whatever form it takes will describe the current situation, problems, and changes a firm is currently undergoing. You need to deduce all tables, columns, and relationships from this preparation. Entities become the tables when a logical design is transferred to a physical database model. Each entity is made up of a number of properties or attributes that will later become the column definitions of the table. A relationship between entities creates a logical link between the entities and in the physical design will represent the Primary Key and Foreign Key relationship of a constraint. REVIEW BREAK

If you looking for unlimited one inclusive web hosting plan please check web hosting plan website.

66 Part I EXAM PREPARATION Entity and attribute names should not exceed 128 characters because a table in SQL Server is restricted to that. In fact, names should be kept as small as possible while still providing a meaningful object title. Remember, entities will become tables in SQL Server. Entity names should be unique. Attributes within the same entity should not be the same as any other object name; any naming in SQL Server should be kept unique. Chapter 3 goes into more detail on this point. Appropriate naming conventions are often developed as a corporate or development standard within an organization. Often mechanisms for shortening names or using common prefixing or suffixing may all be part of a programming team s standard. Attribute Makeup Now that you understand how to properly name attributes, it s time to look at how to break attributes down, a process more commonly known as decomposing attributes. Decomposing an attribute takes an attribute from its original form and divides it into its components. A good example of this is the breaking down of the Address attribute. An Address attribute may easily be broken down into attributes that store data as follows: Street. Stores the street address of the user. City. Stores where the user lives. State. Stores which state the user lives in. Zip. Stores the user s zip code. This process of decomposing an attribute helps you develop a normalized database structure as defined later in this chapter during the discussion of the physical design. Decomposing is also a function of usage as well. If, for example, a person s name is only needed in its full form, then a decision may be made to not break it up into the separate attributes of first and last name. Decomposing an attribute provides many benefits in contrast to general, built attributes that are not decomposed. First off, data integrity is improved. Data integrity is a measurement of how well data is kept consistent and flawless throughout the whole ER model.

For reliable and cheap web hosting services please check tomcat web hosting website.

Chapter 2 DATABASE DESIGN FOR SQL SERVER 2000 65 remember that this simulation is missing a lot of information, so there are likely to be many more attributes than those listed. Naming Attributes For the case study of Lloyd s Hospital, attributes are defined as shown in Figure 2.2. To create and identify attributes is a developed skill; there is no set method for doing so you just need to practice. However, there are some good guidelines available that will help you in creating and choosing the best attributes for your data model. The first is how you name your entities. A good name makes an attribute look professional and helps in its readability. Here are some good guidelines that help in naming entities. Remember to always follow these to keep your design consistent. An entity or an attribute should be named in its singular form, thereby implying that it is only a single instance. An instance is a single occurrence of an entity. The use of underscores ( _ ) in naming attributes is not a good habit because special characters have particular meanings in some software packages, and the mixture of text and other characters is difficult to type. Try to distinguish a word from another by using mixed case, as in DeliveryService rather than Delivery_Service. Patien. Traine. Cours. Nam. Nam. Nam. Addres. Addres. Locatio. Gende. Gende. Cos. Dat. Registere. Dat. Registere. Availabl. Dat. o. Birt. Dat. o. Birt. SS. SS. Nam. o. Cours. Paymen. Du. Employe. Medicin. Machiner. Nam. Nam. Nam. Addres. Locatio. Locatio. Gende. Shel. Purpos. Dat. o. Birt. Ove. Counte. SS. Controlle. Contrac. Typ. FIGURE 2.2 Salar. Attributes implementation in an Entity Positio. Relationship diagram (ER diagram).

If you looking for unlimited one inclusive web hosting plan please check web hosting plan website.

64 Part I EXAM PREPARATION Designing a good database at this stage still does not require you to acquire the knowledge of how databases are implemented in SQL Server; rather it depends on how well you can comprehend a given scenario and its elements. The next section takes a look at entity composition and the defining of attributes. Defining Attributes Identifying attributes is the next step in ensuring a successful data modeling process, and moreover is a part of the Microsoft exam objectives for this chapter, defining entity composition, and the sub- objective, specify entity attributes. Attributes are characteristics or properties defined within a single entity, and they correspond to real-world properties of a place, thing, or concept. An attribute candidate is usually found by examining sentences in the case study that are in close proximity to its residing entity. For instance, take a look at this excerpt from the Lloyd s Hospital case study: We have 300 employees, most of whom work with a permanent contract, but some work on an hour-to-hour basis. Think of attributes as properties of entities. Therefore, from the excerpt above, you probably should have guessed Contract as being a good candidate. because it is a property of an employee. Some common issues in database design are: Should this be an entity or an attribute? Should this be an entity or a relationship? One might model the Contract as an entity, and later make the relationship Employees work under a Contract. This is not the model used in this chapter, but it is worth mentioning that there are many different ways to model the same information. Each variation of a model may work and be correct. This is part of what makes data modeling a painstaking process that requires patience. Whatever model is chosen, try not to jump ahead to the next stage of model- TIPEXAM Attributes on the Exam On the ing until you have considered some alternatives. exam you will not have to dream up Try to find out the attributes that fit each of the Patient, Employee, your own attributes, but you may have to select from a list of poten- Medicine, Machinery, Course, and Trainee entities. Test your results tial attributes and place them into with the results provided in Figure 2.2. If you missed an attribute or the correct entity. added an extra attribute that isn t found below, don t feel that it is wrong. Attribute decision is a hard choice and is varied from person to person, depending on your perspective on the scenario. Also

For reliable and cheap web hosting services please check cheap web hosting website.