Can a multivalued attribute be a primary key

Here we describe and illustrate a 6-step procedure for deriving a relational database schema from an E-R diagram. To illustrate, we use the University E-R diagram in Figure 3.

Uniqueness of rows: keys

It is assumed that the reader is familiar with E-R diagrams, which depict entity sets as rectanglesattributes as ovalsand relationship sets as diamonds. The reader should also be familiar with the relational data modelthe main concepts of which include relationtupleand attribute and their "physical" manifestations: tablerowand columnrespectively.

Step 1: Strong Entity Sets. For each strong i. For each single-valued attribute A of E having no multi-valued subattributes, make all its atomic subattributes including only A itself, if it is atomic be attributes of E.

Choose as the primary key of E the collection of attributes arising from some key of E. With respect to the University E-R diagram in Figure 3. Imagine that either the Student or Faculty or both entity set had name as a composite attribute with atomic subattributes firstName and lastName. Then our translation would have been the same as just described.

Bolt torque calculation pdf

Step 2: Weak Entity Sets. For each weak entity set W in the E-R diagram, create a relation W. As in Step 1, for each single-valued attribute of W having no multi-valued subattributes, make all its atomic subattributes be attributes of W. Let Ewith corresponding relation Ebe an owner entity set of W. Add as foreign key attributes of W the attributes forming the primary key of E.

These attributes can be renamed, if desired. The primary key of W includes all the foreign key attributes added as a result of the previous paragraph, plus any partial key attributes of weak entity set W. However, let us suppose for the sake of this illustration that the Student entity set were existence dependent upon Department via the HasMajor relationship set.

Entities, Primary Keys and Multivalued Attributes

See Section 3. To express this idea, the E-R diagram would be modified so that HasMajor appeared in a double-diamond and Student appeared in a double rectangle. Also, the min-max notation would say 1,1 rather than 0,1 to indicate that every student was related via HasMajor to exactly one department rather than either zero or one.

In terms of the application, that would mean that a student could not exist without having a major offered by some department. Which means that the tuples of relation Student would include not only the partial key attribute stuId but also a foreign key referring to the relation Department.

Home theater remote control touch screen

As the primary key of Department is deptNamewe include an attribute of the same name in Student to act as a foreign key. We could have chosen a different name, of course.

The primary key of Student includes its partial key stuId and the foreign key.By using our site, you acknowledge that you have read and understand our Cookie PolicyPrivacy Policyand our Terms of Service.

Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up. I am designing a database system for my current project and got this situation. I am having a functional dependency as.

Here D is multi valued attribute. D can be i,j,k or i,j or any other combination. And Unique combination of D will define unique value of E.

So i am interested in knowing what is the best way of achieving this requirement. Just to be clear, you can have multi-value primary keys. So, you must be unique on i,j,k. That makes sense. This can provide a linear map to E in your example. That's where you've lost me. That doesn't make sense. If you want them such that reordering is also not allowed, for instance.

can a multivalued attribute be a primary key

Such that these are rejected. Multivalued attributes cannot work as primary keys. Actually, multivalued attributes violate First Normal Form.

Subscribe to RSS

So the good idea is to create a separate table and push multivalued attribute into it. Use fictional primary key i. Look here for the example. Sign up to join this community.

The best answers are voted up and rise to the top. Home Questions Tags Users Unanswered. Asked 4 years, 5 months ago. Active 3 years ago. Viewed 3k times. Active Oldest Votes.

This can provide a linear map to E in your example Here D is multi valued attribute. Evan Carroll Evan Carroll First of all, you forgot to mention the requirement itself. Yggaz Yggaz Why do composite keys violate First Normal Form? Can you elaborate on why you think this is the case? Usually multivalued attributes refers to more than one values stored in the same column.

That said, the suggestion after that completely ignores composite keys and makes little sense.To login with Google, please enable popups. Sign up. To signup with Google, please enable popups. Sign up with Google or Facebook. To sign up you must be 13 or older.

can a multivalued attribute be a primary key

Terms of Use and Privacy Policy. Already have an account? Log in. Get started today! Chapter 4 Quiz. Edit a Copy. Study these flashcards. Jennifer A. What is the ideal number of attributes used to make up a primary key? Which of the following might be represented with a multivalued attribute?

can a multivalued attribute be a primary key

Person's educational background. Which of the following might be represented with a single-valued attribute? Which of the following should be a derived attribute? How is a derived attribute indicated in the Chen Model? When loading data into two entities with a 1:M relationship, how should the data be loaded?

Suppose that Tiny College offers several courses; each course has several classes. What cardinality is Indicated by the Crow's Foot symbol that contains a line beside a crow's foot shape? Which ERD design activity comes first? Create a detailed narrative of the organization's description of operations.

Popular Study Materials from Information Systems database systems pq 1 chapter 2 quiz chapter 3 quiz chapter 6 quiz chapter 6 quiz chapter 1 quiz chapter 1 quiz chapter 1 quiz chapter 2 quiz cmp final. Sign up for free and study better. Anytime, anywhere. Find materials for your class:. Download our app to study better.Database tables are an implementation of relations.

can a multivalued attribute be a primary key

Relations are defined to be sets of tuples and as such have all the properties of sets. Unfortunately SQL tables are not natively sets unless uniqueness constraints are placed on them. This article continues a discussion of uniqueness constraints, specifically it formalizes the notion of super key, primary key, and candidate key.

Remember that a super key is any set of attributes whose values, taken together, uniquely identify each row of a table—and that a primary key is the specific super key set of attributes that we picked to serve as the unique identifier for rows of this table.

We are showing the PK attributes in the scheme diagram for convenience, understanding that keys are a constraint on the table relation.

In fact, not every super key can be selected to be a primary key; those that can be selected are known as candidate keys. Before picking the PKwe need to identify any candidate key that we can find for a table.

Whether a set of attributes constitutes a CK or not depends entirely on the data in the table—not just on whatever data happens to be in the table at the moment, but on any set of data that could realistically be in this table over the life of the database.

Can it uniquely identify anyone we would want as a customer? It does, but only if we make some assumptions. Below are some key observations about this set of attributes. Imagine one customer placing just ten orders, their name and phone number is in the database eleven times: once in the Customers table and once for each of the ten orders placed in the Orders table. Remember that we purposely designed the Customers table without considering its association with Orders.

These rules apply to surrogate and substitute keys that you and your co-workers add to your own tables. However, you might find that a class already has an attribute that appears to be a surrogate or substitute key, but has been defined by someone else—usually a standards-setting organization or a government agency.

We call this attribute an external key. In these situations, you may use it as a descriptive attribute in both UML and thus also in the relation scheme diagram. Such attributes need to be well documented.

Royal road

Like other descriptive attributes, an external key might or might not become part of a candidate key in your database. There is one special case of an external key that requires careful handling in your database design: the United States social security number SSN. Originally intended for use only to identify social security participants, it has now become so over-used as an identifier that access to it poses risks of serious damage to individuals, even including identity theft.

Please do not ever use the SSN in your database unless you are required to do so by law for example, to file tax information. Even then, do not use it as a primary key that would be viewable to everyone who can access your database. Similar care should be taken with other sensitive information such as state identification, credit card numbers, bank account numbers, etc.

Remember, a UML class diagram never includes implementation details such as PK, CK, and FK information ; all of these are artifacts of implementing the UML class diagram by mapping it to the relational model and thus must be specified in the relational database scheme diagram.

Our revised scheme, with surrogate PK customer ID is given below. If this constraint is missing, then, the relation scheme is incorrect. In the child table, it can be used as if it were a naturally occurring attribute of the table and thus can be in a candidate key or primary key, as demonstrated here. The Orders table now no longer has the name and phone number of the customer placing the order.

Instead, we have an integer identifying the customer.Is it possible for an attribute to be both composite and multivalued? I guess, but that's not really important. Ultimately, multi-valued attributes become a weak entity and then a table with a foreign key relation to the original entity.

Yes, but you have to decide that if its sub-parts more than 3 or 4 than consider as "Multi-valued" attribute or if their sub-parts are 3 or 4 then use it as "Composite" attribute. Wednesday, October 26, Composite Vs. Multi Valued Attributes. Question :: Composite Vs. For example, an attribute name could be structured as a composite attribute consisting of first-name, middle-initial, and last-name. Multivalued: There may be instances where an attribute has a set of values for a specific entity.

Consider an employee entity set with the attribute phone-number. An employee may have zero, one, or several phone numbers, and different employees may have different numbers of phones. Phone Number. Unknown February 3, at PM. Dan April 14, at AM.

Jamal Naser January 4, at PM. Unknown May 6, at AM.

Esercizi dimmaginazione. note su arendt e la letteratura .

Unknown August 24, at AM. Suchitra January 28, at AM. Unknown October 1, at AM. Aravind November 21, at PM. Newer Post Older Post Home. Subscribe to: Post Comments Atom.By using our site, you acknowledge that you have read and understand our Cookie PolicyPrivacy Policyand our Terms of Service. The dark mode beta is finally here. Change your preferences any time. Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information.

Functional dependencies are the attributes that their values are determined in a unique way by another attribute. Given that, can a multivalued attribute be dependent upon a primary key? Find a way to say it correctly or how can you understand it?

There is no such thing as a "multivalued attribute" in a relation. A tuple has an attribute value for each attribute name. Maybe you mean, a set of attributes is being determined? Maybe you mean, a multi-valued dependency? If you have an attribute that you consider to contain multiple parts, ie you want to generically query about the parts without using operators with parameters of their types, then it is usually good design to have a separate table with attributes for those parts.

But that's not addressed by normalization. Similarly, if you have a bunch of attributes that play a similar role often with similar names then it is usually good design to have a separate table with just one attribute for the role. PKs don't. You can pick one CK as "the PK" but its primariness is irrelevant to the relational model.

It might be relevant to some information modeling method or product. Superkeys are sets of columns that determine every column. Since every set of attributes always determines the attributes in it, superkeys are sets of columns that determine every other column. CKs are superkeys that contain no smaller superkey. So CKs are sets of columns that are unique but contain no smaller set of columns that are unique.

You don't know all the CKs until you find all the FDs. But you might know that a particular set of attributes is unique and has no smaller unique set, so that you know that it is a CK and you can call it "the PK". Eg an id attribute in a relation variable that can have more than one row.By using our site, you acknowledge that you have read and understand our Cookie PolicyPrivacy Policyand our Terms of Service.

The dark mode beta is finally here. Change your preferences any time. Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. Can I have a primary key without clustered index? I am aware that when we create primary key constraint on a column, it by default creates a clustered index. So in that case, how should I deactivate clustered index? Can I have a clustered index with multiple columns together?

Like in non-clustered where I can join different columns for a single non-clustered index. As you mentioned, a primary key constraint is backed by a clustered index by default. You can tell SQL Server to back the constraint with a nonclustered index by declaring the constraint as follows:. Yes, you can define an index with more than one column in the index key. It's really no different than a nonclustered index. They have different capabilities and different syntax.

Learn more. Also can I have multivalued clustered index? Ask Question. Asked 7 years, 8 months ago. Active 7 years, 8 months ago. Viewed 6k times. Folks, I would like to understand the answer for the following questions: Can I have a primary key without clustered index?

Learner Learner 4, 13 13 gold badges 41 41 silver badges 94 94 bronze badges. Active Oldest Votes. I know nothing about MySQL. Jon Seigel Jon Seigel In both cases, PrimaryKeys and Indexes clustered or not can cover multiple fields. MatBailie MatBailie Also, InnoDB always has a clustered index even if you don't have a primary key.

Dems: Hi Dems, thank you for the answer. And, how do I create a PK without Clluetersted index? I mean if I just create a table with syntax and mentioning primary key and or using the GUI tool for creating table wherein I just tick the column I need as primary key.

I am really new : Hope you understand what exactly I am behind : I need to know the concrete way of achieving it. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown.


Leave a Reply

Your email address will not be published. Required fields are marked *