Goal of design is to generate a formal specification of the database schema
Methodology:
Use E-R model to get a high-level graphical view of essential components of enterprise and how they are related
Then convert E-R diagram to SQL DDL, or whatever database model you are using
E-R Model is not SQL based. It's not limited to any particular DBMS. It is a conceptual and semantic model – captures meanings rather than an actual implementation
The E-R Model: The enterprise is viewed as set of
Entities
Relationships among entities
Symbols used in E-R Diagram
Entity – rectangle
Attribute – oval
Relationship – diamond
Link - line
Entities and Attributes
Entity: an object that is involved in the enterprise and that be distinguished from other objects. (not shown in the ER diagram--is an instance)
Can be person, place, event, object, concept in the real world
Can be physical object or abstraction
Ex: "John", "CSE305"
Entity Type: set of similar objects or a category of entities; they are well defined
A rectangle represents an entity set
Ex: students, courses
We often just say "entity" and mean "entity type"
Attribute: describes one aspect of an entity type; usually [and best when] single valued and indivisible (atomic)
Represented by oval on E-R diagram
Ex: name, maximum enrollment
May be multi-valued – use double oval on E-R diagram
May be composite – attribute has further structure; also use oval for composite attribute, with ovals for components connected to it by lines
May be derived – a virtual attribute, one that is computable from existing data in the database, use dashed oval. This helps reduce redundancy
Entity Types
An entity type is named and is described by set of attributes
Student: Id, Name, Address, Hobbies
Domain: possible values of an attribute.
Note that the value for an attribute can be a set or list of values, sometimes called "multi-valued" attributes
This is in contrast to the pure relational model which requires atomic values
E.g., (111111, John, 123 Main St, (stamps, coins))
Key: subset of attributes that uniquely identifies an entity (candidate key)
Entity Schema:
The meta-information of entity type name, attributes (and associated domain), key constraints Entity Types tend to correspond to nouns; attributes are also nouns albeit descriptions of the parts of entities
May have null values for some entity attribute instances – no mapping to domain for those instances
Keys
Superkey: an attribute or set of attributes that uniquely identifies an entity--there can be many of these Composite key: a key requiring more than one attribute Candidate key: a superkey such that no proper subset of its attributes is also a superkey (minimal superkey – has no unnecessary attributes) Primary key: the candidate key chosen to be used for identifying entities and accessing records. Unless otherwise noted "key" means "primary key" Alternate key: a candidate key not used for primary key Secondary key: attribute or set of attributes commonly used for accessing records, but not necessarily unique Foreign key: term used in relational databases (but not in the E-R model) for an attribute that is the primary key of another table and is used to establish a relationship with that table where it appears as an attribute also.
So a foreign key value occurs in the table and again in the other table. This conflicts with the idea that a value is stored only once; the idea that a fact is stored once is not undermined.
Graphical Representation in E-R diagram
Rectangle -- Entity Ellipses -- Attribute (underlined attributes are [part of] the primary key) Double ellipses -- multi-valued attribute Dashed ellipses-- derived attribute, e.g. age is derivable from birthdate and current date.
[Drawing notes: keep all attributes above the entity. Lines have no arrows. Use straight lines only]
Relationships
Relationship: connects two or more entities into an association/relationship
"John" majors in "Computer Science"
Relationship Type: set of similar relationships
Student (entity type) is related to Department (entity type) by MajorsIn (relationship type).
Relationship Types may also have attributes in the E-R model. When they are mapped to the relational model, the attributes become part of the relation. Represented by a diamond on E-R diagram.
Relationship types can have descriptive attributes like entity sets
Relationships tend to be verbs or verb phrases; attributes of relationships are again nouns
[Drawing tips: relationship diamonds should connect off the left and right points; Dia can label those points with cardinality; use Manhattan connecting line (horizontal/vertical zigzag)]
Attributes and Roles
An attribute of a relationship type adds additional information to the relationship
e.g., "John" majors in "CS" since 2000
John and CS are related
2000 describes the relationship - it's the value of the since attribute of MajorsIn relationship type
The role of a relationship type names one of the related entities. The name of the entity is usually the role name. e.g., "John" is value of Student role, "CS" value of Department role of MajorsIn relationship type
(John, CS, 2000) describes a relationshipProblem: relationships can relate elements of same entity type
e.g., ReportsTo relationship type relates two elements of Employee entity type:
Bob reports to Mary since 2000
We do not have distinct names for the roles. It is not clear who reports to whom.
Solution: the role name of relationship type need not be same as name of entity type from which participants are drawn
ReportsTo has roles Subordinate and Supervisor and attribute Since
Values of Subordinate and Supervisor both drawn from entity type Employee
Optional to name role of each entity-relationship, but helpful in cases of
Recursive relationship – entity set relates to itself
Multiple relationships between same entity sets
Roles are edges labeled with role names (omitted if role name = name of entity set). Most attributes have been omitted.
Relationship Type
Relationship types are described by the set of roles (entities) and [optional] attributes
e.g., MajorsIn: Student, Department, Since
Think that entities are nouns; relationship types are often verbs
students and departments are the entities (nouns) and roles in relationship types
majors is the relationship type (verb)
i.e., "student" "majors in " "department"
Here we have equate the role name (Student) the name of the entity type (Student) of the participant in the relationship.
Degree of relationship
The number of roles in the relationship Binary – links two entity sets; set of ordered pairs (most common) Ternary – links three entity sets; ordered triples (rare). If a relationship exists among the three entities, all three must be present N-ary – links n entity sets; ordered n-tuples (very rare). If a relationship exists among the entities, then all must be present. Cannot represesnt subsets.
Note: ternary relationships may sometimes be replaced by two binary relationships (see book Figures 3.5 and 3.13). Semantic equivalence between ternary relationships and two binary ones are not necessarily true.
Cardinality of Relationships
Cardinality is the number of entity instances to which another entity set can map under the relationship. This does not reflect a requirement that an entity has to participate in a relationship. Participation is another concept. One-to-one: X-Y is 1:1 when each entity in X is associated with at most one entity in Y, and each entity in Y is associated with at most one entity in X. One-to-many: X-Y is 1:M when each entity in X can be associated with many entities in Y, but each entity in Y is associated with at most one entity in X. Many-to-many: X:Y is M:M if each entity in X can be associated with many entities in Y, and each entity in Y is associated with many entities in X ("many" =>one or more and sometimes zero)
Relationship Participation Constraints
Total participation
Every member of entity set must participate in the relationship
Represented by double line from entity rectangle to relationship diamond
E.g., A Class entity cannot exist unless related to a Faculty member entity in this example, not necessarily at Juniata.
You can set this double line in Dia
In a relational model we will use the references clause.
Key constraint
If every entity participates in exactly one relationship, both a total participation and a key constraint hold
E.g., if a class is taught by only one faculty member.
Partial participation
Not every entity instance must participate
Represented by single line from entity rectangle to relationship diamond
E.g., A Textbook entity can exist without being related to a Class or vice versa.
Existence Dependency and Weak Entities
Existence dependency: Entity Y is existence dependent on entity X is each instance of Y must have a corresponding instance of X
In that case, Y must have total participation in its relationship with X
If Y does not have its own candidate key, Y is called a weak entity, and X is strong entity
Weak entity may have a partial key, called a discriminator, that distinguishes instances of the weak entity that are related to the same strong entity
Use double rectangle for weak entity, with double diamond for relationship connecting it to its associated strong entity
Note: not all existence dependent entities are weak – the lack of a key is essential to definition
Schema of a Relationship Type
Contains the following features:
Role names, Ri, and their corresponding entity sets. Roles must be single valued (the number of roles is called its degree) Attribute names, Aj, and their corresponding domains. Attributes in the E-R model may be set or multi-valued. Key: Minimum set of roles and attributes that uniquely identify a relationship Relationship: <e1, …en; a1, …ak>
ei is an entity, a value from Ri’s entity set
aj is a set of attribute values with elements from domain of Aj
ER Diagram Example
Customize Password Recovery Control Password Format in Asp.net
By default,SqlMembershipProvider is configured to store users' passwords as clear text. This setting is controlled by the passwordFormat attribute in the web.config file, as seen here: Code: <membership defaultProvider="SqlMembershipProvider"> <providers> <clear/> <add name="SqlMembershipProvider" ... passwordFormat="Clear" ... </providers> </membership> There are three options: Clear, Hashed, and Encrypted. Clear passwords allow for very fast authentication on the server, allow the "Forgot my password" feature to e-mail passwords to users, and can make troubleshooting by administrators a bit easier. However, it is not as secure as hashing or encryption. If user security is important, you can hash or encrypt the passwords. Hashed passwords are hashed using a one-way hash algorithm and a randomly generated salt value. This is very secure, but the password is not recoverable once it is hashed. This means you cannot use the Forgot my password feature, and an administrator cannot change the password on the Manage users page in the Site admin area (an administrator can, however, use the Reset password function to reset the password to a random value). An alternative to hashing is to set the password format to Encrypted. The passwords are encrypted, which provides excellent security and can also be decrypted by SqlMembershipProvider. This means a user who forgets his password can have it e-mailed without any administrator intervention. For many, this is the best option, but to get it working in Gallery Server Pro you have to do a little bit of manual work. After setting passwordFormat to "Encrypted" in web.config, you will get the following message when trying to create a new user: Hint: You must specify a non-autogenerated machine key to store passwords in the encrypted format. Either specify a different passwordFormat, or change the machineKey configuration to use a non-autogenerated decryption key. Don't worry, this is a good thing! Microsoft is protecting you from shooting yourself in the foot. If it didn't throw an error, your users would be created with an encryption key that isn't discoverable and is not transportable to other machines. In other words, your user database becomes chained to the server on which it is created and cannot be migrated elsewhere! This is definitely Not A Good Thing. By default, ASP.NET uses machine-generated keys for encryption, as configured in the server-wide machine.config file: Code: <machineKey validationKey="AutoGenerate,IsolateApps" decryptionKey="AutoGenerate,IsolateApps" validation="SHA1" decryption="Auto" /> The solution is to explicitly specify encryption keys, either in machine.config (safer) or Gallery Server's web.config file (less safe). The values go in the <system.web> section and should look something like this: Code: <machineKey validationKey="B6AF9388159183B6A8EE690C851398A11997C519F859599459E30121EF4DF8BF 2B1666BC82BB6FEB2FEF279BEB325E897AED6EA5B71C55D213B76510EF96CAA8" decryptionKey="3C5EF273A20ED2473594C9691627700A79376A7C1455320A" validation="SHA1" /> If you want to generate your own keys. There are many code samples how to do this and at least (http://aspnetresources.com/tools/machineKey) to help you out. Once you add the keys, any new users you create will have encrypted passwords. Note that when a user is created, the password format that is active at that time determines the format for that user's password for the lifetime of the user's account. Even if you change the format to another value, that user's password is never updated to reflect the new scheme. For example, if you created an Admin user when you installed SqlMembershipProvider, it's password is forever stored as clear text, even if you change the password or reset it. The only way around this is to delete the user and recreate it.
By default,SqlMembershipProvider is configured to store users' passwords as clear text. This setting is controlled by the passwordFormat attribute in the web.config file, as seen here:
Clear passwords allow for very fast authentication on the server, allow the "Forgot my password" feature to e-mail passwords to users, and can make troubleshooting by administrators a bit easier. However, it is not as secure as hashing or encryption. If user security is important, you can hash or encrypt the passwords.
Hashed passwords are hashed using a one-way hash algorithm and a randomly generated salt value. This is very secure, but the password is not recoverable once it is hashed. This means you cannot use the Forgot my password feature, and an administrator cannot change the password on the Manage users page in the Site admin area (an administrator can, however, use the Reset password function to reset the password to a random value).
An alternative to hashing is to set the password format to Encrypted. The passwords are encrypted, which provides excellent security and can also be decrypted by Gallery Server Pro. This means a user who forgets his password can have it e-mailed without any administrator intervention. For many, this is the best option, but to get it working in Gallery Server Pro you have to do a little bit of manual work.
After setting passwordFormat to "Encrypted" in web.config, you will get the following message when trying to create a new user:
Hint:
You must specify a non-autogenerated machine key to store passwords in the encrypted format. Either specify a different passwordFormat, or change the machineKey configuration to use a non-autogenerated decryption key.
Microsoft is protecting you from shooting yourself in the foot. If it didn't throw an error, your users would be created with an encryption key that isn't discoverable and is not transportable to other machines. In other words, your user database becomes chained to the server on which it is created and cannot be migrated elsewhere! This is definitely Not A Good Thing.
By default, ASP.NET uses machine-generated keys for encryption, as configured in the server-wide machine.config file:
The solution is to explicitly specify encryption keys, either in machine.config (safer) or Server's web.config file (less safe). The values go in the <system.web> section and should look something like this:
If you want to generate your own keys. There are many code samples how to do this and at least one web site to help you out.
Once you add the keys, any new users you create will have encrypted passwords and You Will receive
original Password (decrypted) in your Mail.
Note that when a user is created, the password format that is active at that time determines the format for that user's password for the lifetime of the user's account. Even if you change the format to another value, that user's password is never updated to reflect the new scheme. For example, if you created an Admin user when you installed Gallery Server Pro, it's password is forever stored as clear text, even if you change the password or reset it. The only way around this is to delete the user and recreate it.
How to Customize Password Recovery Control Message Format:
When we use Password Recovery control in asp.net for your login module, We want to consider 3 facts,
