HW 2 with answers Fall 2016.docx

HW 2 with answers Fall 2016.docx - ISM 6215 Business...

Info icon This preview shows pages 1–6. Sign up to view the full content.

ISM 6215 - Business Database Systems I Fall 2016 Home Work – 2 (Marks 100) Due Date – Friday 4 th October by 11 AM in STZ 351 (ISOM Office) Question 1 - Contrast the following terms 1. Base Table versus View 2. Normalization versus Denormalization (2 * 2.5 = 5 marks) Answer 1 - 1. Base table; view. A view is a virtual table and is not permanently represented in storage. In contrast a base table is stored in the secondary storage in database. A view definition is only stored, and the contents of the view are calculated each time the view is referenced in a query. . 2. Normalization; Denormalization. Normalization is the process of breaking up larger relations with data maintenance issues (i.e., anomalies) into smaller, well-structured relations. Denormalization is the opposite process of normalization, as it takes smaller relations and combines them into bigger relations to produce relations that are processed with less management overhead for queries, reporting, and sometimes data maintenance purposes. NOTE- So long a student writes the definition of the two terms clearly, s/he should be given full marks.
Image of page 1

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

Question 2 – NOTE – In Movie Nbr 567, the character of George Bailey is played by James Stewart (actor Name) having Actor ID 245 , the character of Mary Bailey is played by Donna Reed (Actor name) having Actor ID of 236. The name of characters are unique in a movie but the same character name can appear in different movies. A movie can have several licenses (it is same as several copies) and each license (copy) of a movie is uniquely identified by Movie Copy Nbr (or Movie license number). Based on above, answer the following
Image of page 2
Draw the relational schema and show the functional dependencies in it (Both partial and transitive). Normalize the relation into 3NF and clearly show the referential integrity. (10 +15= 25 marks) Answer 2 - a. Functional dependencies (Movie license is written as movie copy) This relation is not in 3NF as there are a number of issues with its structure. In fact, due to the repeating groups indicated by the sample data (multiple characters listed in the curly bracketed information), this relation is not even in first normal form (1NF). There are partial functional dependencies in this relation as the full primary key (from existing attributes) would be comprised of MovieNbr, MovieCopyNbr, and character attributes that individually determine other attributes in the relation. None of the non-key attribute is completely determined by the primary key (MovieNbr, MovieCopyNbr, character) . Transitive dependencies also exist with DirectorID determining Director information, StudioID determining Studio information, and ActorID determining Actor information. For the set of relations to be in 3NF, these dependencies in the data must be removed.
Image of page 3

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

Image of page 4
b. Set of relations in 3NF; For further clarification the above diagram can be shown as below (This is not needed in the answer). Primary key is underline and foreign key in italics.
Image of page 5

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

Image of page 6
This is the end of the preview. Sign up to access the rest of the document.
  • Spring '14
  • Bandyopadhyay,Subhajyoti
  • Relational model, Cruise ship, key value

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern