Homework Solution: 1. Consider the following scenario: an IT department at a large company must keep information about workstations depl…

    1. Consider the following scenario: an IT department at a large company must keep information about workstations deployed throughout the company. For each workstation, the following information must be kept : ◦ inventory number (This is uniquely assignment by the IT department to each workstation it purchases and is etched onto the workstation.) ◦ product serial number (This is uniquely assigned by the workstation vendor and is on a panel in the rear of the workstation. Serial numbers of different vendors may overlap; that is, workstations of two different vendors could have the same serial number.) ◦ workstation vendor (This is the name of the vendor of the workstation. All vendors have different names.) ◦ date purchased ◦ purchase price ◦ amount of RAM ◦ size of disk storage ◦ Solid state drive or hard drive ◦ location (A room number, which is a three digit number.) a. Write a a relational table schema for this information. Include a table name along with the column names and column domains as part of your schema. Use this format: Table_name(col1: domain1; col2:domain2: ... ; coln:domainn) b. Which column or set of columns can be used to uniquely identify the tuples in the table? Use this information to identify the candidate keys for the table. c. Identify all of the super keys derived from the candidate keys from part b. d. Identify the one primary key and any alternate keys from the candidate keys from part b. NOTE: PLEASE PROVIDE ME THE ANSWERS FOR a, b, c, d

    Expert Answer

     
    Question a) Workstations ( Inventory_Number: Number(5), Serial_Num

    1. Consider the restraintthcoming scenario: an IT division at a abundant posse must tend counsel encircling workstations deployed throughout the posse. Restraint each workstation, the restraintthcoming counsel must be kept

    : ◦ schedule sum (This is uniquely ordinance by the IT division to each workstation it restraintfeitures and is etched onto the workstation.)

    ◦ issue serial sum (This is uniquely assigned by the workstation vendor and is on a panel in the change of the workstation.

    Serial sums of irrelative vendors may overlap; that is, workstations of couple irrelative vendors could bear the similar serial sum.)

    ◦ workstation vendor (This is the spectry of the vendor of the workstation. Every vendors bear irrelative spectrys.)

    ◦ end restraintfeitured

    ◦ restraintfeiture price

    ◦ aggregate of RAM

    ◦ bigness of disk storage

    ◦ Solid specify importune or flinty importune

    ◦ residuum (A space sum, which is a three digit sum.)

    a. Write a a abstract board schema restraint this counsel. Include a board spectry parallel with the shaft spectrys and shaft domains as keep-akeep-amultiply of your schema. Use this restraintmat: Board_name(col1: domain1; col2:domain2: … ; coln:domainn)

    b. Which shaft or firm of shafts can be used to uniquely authenticate the tuples in the board? Use this counsel to authenticate the candiend clews restraint the board.

    c. Authenticate every of the super clews multiplyial from the candiend clews from keep-akeep-amultiply b.

    d. Authenticate the individual original clew and any vacillate clews from the candiend clews from keep-akeep-amultiply b.

    NOTE: PLEASE PROVIDE ME THE ANSWERS FOR a, b, c, d

    Expert Defense

     

    Question a)

    Workstations ( Schedule_Number: Sum(5), Serial_Number: Varchar2(16), Vendor: Varchar2(20), Restraintfeiture_Date: End, Price: Sum(10, 2), RAM: Sum(3), Disk_Storage: Varchar2(10), SSD_HDD: Varchar2(10), Residuum: Sum(3)

    Question b)

    To authenticate the tuples, the clews can be:

    • Inventory_Number
    • (Vendor, Serial_Number)

    Question c)

    Every super clews that can be generated from keep-akeep-amultiply b,

    • Inventory_Number, Restraintfeiture_Date, Price, RAM, Disk_Storage, SSD_HDD, Residuum
    • Vendor, Serial_Number, Restraintfeiture_Date, Price, RAM, Disk_Storage, SSD_HDD, Residuum

    Question d)

    Original clew: Schedule_Number

    Another Candiend clew: (Vendor, Serial_Number)