Category: Databases

Define the Entity Integrity Constraint and Referential Integrity Constraint then explain their differences in a relational database. Explain why the DELETE operation may violate only the referential integrity and not the entity constraint. Provide examples to illustrate each constraint using the database system for SEU from question 1.
you can check the other questions in the file / please Read the instructions carefully / No plagiarism
· You must submit two separate copies (one Word file and one PDF file) using the Assignment Template on Blackboard via the allocated folder. These files must not be in compressed format.
· It is your responsibility to check and make sure that you have uploaded both the correct files.
· Zero mark will be given if you try to bypass the SafeAssign (e.g. misspell words, remove spaces between words, hide characters, use different character sets, convert text into image or languages other than English or any kind of manipulation).
· Email submission will not be accepted.
· You are advised to make your work clear and well-presented. This includes filling your information on the cover page.
· You must use this template, failing which will result in zero mark.
· You MUST show all your work, and text must not be converted into an image, unless specified otherwise by the question.
· Late submission will result in ZERO mark.
· The work should be your own, copying from students or other resources will result in ZERO mark.
· Use Times New Roman font for all your answers.

Read the following Case Study and answer the following questions:
Genetic data from direct-to-consumer companies like 23andMe and GEDmatch is being used in novel ways, such as solving crimes. What are some possible implications for consumer privacy and consent when such data is used without the knowledge or consent of the individuals who provided it?
Considering what we’ve learned about the value of assets and risk assessment, how would you evaluate the value of genetic data from an information systems perspective?
As per the case study, misuse of personal data can lead to severe impacts such as identity theft, extortion, and financial loss. Given what you know about risk mitigation strategies, what measures could companies like 23andMe or GEDmatch implement to better protect the genetic data of their users?
With regards to the control mechanisms we’ve discussed in the course, how could the companies that manage such sensitive genetic information incorporate administrative, technical, and physical controls to enhance their data security?
How should policies around the use of genetic data evolve as we continue to understand its value and potential uses better? What ethical considerations should companies and law enforcement take into account when handling genetic data?
Your submission must be between 750-1500 words in length in APA format, including a title page, and please use at least two academic resources.
Our Genetic Data: A Double Edged Sword
Personal data is an umbrella term referring to our names, addresses, phone numbers, Social Security numbers, and health and financial records, along with social media posts, location data, search-engine queries, and a myriad of other personal details. These data are highly sensitive because their misuse impacts all of us so severely: for example, identity theft, extortion, financial loss, loss of privacy, and many other negative consequences.
A major challenge associated with keeping our personal data safe is that a variety of entities collect them. In some instances, companies collect, analyze, and sell our personal data without our knowledge or consent. Recall our discussion of data aggregators in Chapter 3.
Sometimes, we provide our data willingly and knowingly, even though the specifics appear in lengthy, hard-to-read terms-of-service agreements. For example, with Google and Facebook, the consumer can use each platform’s functions for free in return for allowing the companies to monetize our data by targeting us with advertisements.
In recent years, scientists have been able to discover genetic data, which is the most personal type of data for each of us. Genetic data refers to the inherited characteristics located in our chromosomal deoxyribonucleic acid (DNA) or ribonucleic acid (RNA). Each of us has a unique genome, which comprises our complete set of DNA, including all of our genes. As with all of our personal data, the problems with our genetic data are: Who has collected it? Who is collecting it now? Who has it? What are they doing with it?
As a result of patients taking a more proactive role in their health care, direct-to-consumer (DTC) laboratory testing is becoming increasingly popular. DTC genetic tests allow consumers to access information about their genetics without involving their health-care professionals.
As of July 2020 the International Society of Genetic Genealogy (www.isogg.org) listed 33 DTC companies that are performing genetic testing. More than 12 million people around the world have utilized the services of one of these companies, such as 23andMe (www.23andme.com). How does this process work? Basically, you send a vial of saliva along with a fee to 23andMe. In return, you receive a report that includes (a) the proportion of your DNA that comes from each of 45 global genetic populations, (b) the origins of your maternal and paternal ancestors, and (c) a feature that allows you to connect with DNA relatives, if you opt in. The report also contains health-care information such as DNA that might not affect your health but could affect the health of your children as well as how your DNA can influence your chances of contracting certain diseases.
In 2010, GEDmatch (www.gedmatch.com) was founded to help amateur and professional genealogists and adoptees searching for birth parents. Users could upload their DNA profiles from DTC companies to identify relatives who had also uploaded their profiles. Participants could hide their names by using aliases. Each account, however, had to include an associated email address. GEDmatch provided results such as the closest matches to a user’s DNA and the estimated number of generations to a common ancestor. By December 2019 more than 1 million people had uploaded their profiles to the site.
Solving Crimes (The Good News)
The police began using GEDmatch to solve crimes, particularly cold cases. A cold case is an unsolved criminal investigation which remains active pending the discovery of new evidence. Until the authorities began using commercial genetic databases to assist with their detective work, the only DNA officially available to them were records of DNA samples provided by individuals involved with the justice system; that is, suspects and convicted criminals. This process of using commercial genetic databases led to an ongoing debate regarding public safety versus privacy concerns—in essence, the good news and the bad news about the use of our genetic data. Let’s look closely at forensic genealogy.
Forensic genealogy is the practice of analyzing genetic data from DTC companies or from companies such as GEDmatch for the purpose of identifying suspects or victims in criminal cases. Forensic genealogy uses genetic genealogy, which is the use of DNA tests in combination with traditional genealogical methods to infer biological relationships between and among individuals. Traditional genealogical methods include the study of families and family history by conducting oral interviews and researching records such as birth and marriage certificates, census data, and newspaper obituaries. The process combines data from those techniques with data from modern sources such as social media platforms, particularly Facebook.
To use forensic genealogy, law enforcement agencies have uploaded crime-scene DNA data to genetic companies to locate possible relatives of potential suspects. Genealogy experts then assemble family trees and analyze demographic identifiers. One company, Parabon NanoLabs (www.parabon-nanolabs.com), is a leader in the use of forensic genealogy as an investigative tool.
After law enforcement agencies have identified potential suspects through forensic genealogy, they use conventional investigative methods such as comparing present physical features to past eyewitness statements and police sketches. This process can often narrow down the choices to a few candidates.
Solving Cold Cases – Example 1
In 1987 a young woman left her family home for what was supposed to be a quick trip with her boyfriend. They did not return the next day, as planned. After several days, her body was found. She had been shot. Two days later, his body was found 75 miles away. He had been strangled. Police found the van they were driving in a third location.
The police collected a semen sample from the woman’s pants. By 1994, DNA analysis had advanced to the point that the semen could produce a genetic profile of the suspect. In 2003, investigators uploaded the profile to CODIS, the FBI’s criminal DNA database. The investigators hoped for a match, but CODIS did not return one.
In 2017, a detective who was examining the department’s cold cases heard that there was a way to obtain more information from DNA. He contacted Parabon. Genealogists there used the 30-year-old DNA sample and found two of the eventual suspect’s second cousins: one on his father’s side of the family, and one on his mother’s side. The firm’s lead genealogist used newspaper archives and marriage records to build a family tree dating back to the two cousins’ great-grandparents. She then found a particular couple who lived about 7 miles from where the boyfriend’s body was found.
The genealogist concentrated on that couple’s only son—who would have been 24 at the time of the murders—as a suspect. Next, an undercover officer who had begun watching the suspect picked up a cup that had fallen off his truck. The DNA from the cup and the DNA from a cheek swab provided by the suspect once in custody matched the DNA gathered 30 years before. He was convicted and sentenced to serve two life sentences concurrently. As of July 2020 he was appealing the verdict.
Solving Cold Cases – Example 2
The trail of the Golden State Killer had gone cold decades ago. The police linked him to more than 50 rapes and at least 12 murders from 1976 to 1986 but he eluded all attempts to find him. However, the police had retrieved a DNA sample from one of the crime scenes and had carefully preserved it over the years.
When the police heard about GEDmatch, they uploaded the suspect’s DNA sample. After four months of close examination, GEDmatch provided matches to relatives of the suspect, although not the suspect himself. Because the site did provide family trees, genealogists were able to find third cousins of the suspect. After examining the family tree to find a common ancestor of the third cousins, they proceeded back down the tree to identify a likely suspect. In addition to the DNA evidence, some of his victims had described him as a 5’9”, 165-pound white male, characteristics that matched his features.
In April 2018 the police arrested the suspect, who was then 72 years old and a former police officer. His DNA matched the sample taken in an earlier crime. In June 2020 he pled guilty to several counts of first-degree murder and in August the court sentenced him to 11 consecutive life sentences without the possibility of parole.
Privacy Concerns (The Bad News)
The use of open-source genetic databases has ignited a debate regarding the Fourth Amendment. This amendment states that a warrant is required in situations that violate an individual’s reasonable expectations of privacy. Given the sensitivity of information surrounding commercial genetic databases, especially regarding familial associations, courts have asserted that individuals are subject to protection under the Fourth Amendment.
Privacy advocates protested that law enforcement agencies were accessing the entire GEDmatch database without the informed consent of the users. As a result, GEDmatch began to require its customers to specifically opt in to allow law enforcement agencies to access their genetic data. The agencies objected, claiming that GEDmatch’s policy change would make it much more difficult to identify suspects and solve cold cases using genetic genealogy.
In September 2019 the U.S. Department of Justice (www.justice.gov) released interim guidelines stating that federal investigators could use forensic genealogy to discover suspects only in serious crimes such as murder and rape. The guidelines also stated that federal investigators must have a search warrant to collect DNA samples from a suspect’s relatives, who must have previously opted in to allow law enforcement agencies to access their genetic data. Because the DOJ is a federal agency, these guidelines did not apply to state or local law enforcement agencies.
In December 2019 forensic for-profit DNA analysis company Verogen (www.verogen.com) bought GEDmatch. Verogen’s CEO asserted that the site would focus on solving crimes, not just connecting family members through their DNA. He further stated that current and future users would have the ability to opt out of criminal DNA searches and that Verogen would fight “future attempts to access the data of those who have not opted in.”
Forensic genealogists then began using Family Tree DNA (www.familytreedna.com) due to the increased difficulty involved in obtaining genetic profiles from Verogen. Family Tree’s policy dictates that customers are automatically opted in unless they choose to opt out.

Read the following Case Study and answer the following questions:
Genetic data from direct-to-consumer companies like 23andMe and GEDmatch is being used in novel ways, such as solving crimes. What are some possible implications for consumer privacy and consent when such data is used without the knowledge or consent of the individuals who provided it?
Considering what we’ve learned about the value of assets and risk assessment, how would you evaluate the value of genetic data from an information systems perspective?
As per the case study, misuse of personal data can lead to severe impacts such as identity theft, extortion, and financial loss. Given what you know about risk mitigation strategies, what measures could companies like 23andMe or GEDmatch implement to better protect the genetic data of their users?
With regards to the control mechanisms we’ve discussed in the course, how could the companies that manage such sensitive genetic information incorporate administrative, technical, and physical controls to enhance their data security?
How should policies around the use of genetic data evolve as we continue to understand its value and potential uses better? What ethical considerations should companies and law enforcement take into account when handling genetic data?
Your submission must be between 750-1500 words in length in APA format, including a title page, and please use at least two academic resources.
Our Genetic Data: A Double Edged Sword
Personal data is an umbrella term referring to our names, addresses, phone numbers, Social Security numbers, and health and financial records, along with social media posts, location data, search-engine queries, and a myriad of other personal details. These data are highly sensitive because their misuse impacts all of us so severely: for example, identity theft, extortion, financial loss, loss of privacy, and many other negative consequences.
A major challenge associated with keeping our personal data safe is that a variety of entities collect them. In some instances, companies collect, analyze, and sell our personal data without our knowledge or consent. Recall our discussion of data aggregators in Chapter 3.
Sometimes, we provide our data willingly and knowingly, even though the specifics appear in lengthy, hard-to-read terms-of-service agreements. For example, with Google and Facebook, the consumer can use each platform’s functions for free in return for allowing the companies to monetize our data by targeting us with advertisements.
In recent years, scientists have been able to discover genetic data, which is the most personal type of data for each of us. Genetic data refers to the inherited characteristics located in our chromosomal deoxyribonucleic acid (DNA) or ribonucleic acid (RNA). Each of us has a unique genome, which comprises our complete set of DNA, including all of our genes. As with all of our personal data, the problems with our genetic data are: Who has collected it? Who is collecting it now? Who has it? What are they doing with it?
As a result of patients taking a more proactive role in their health care, direct-to-consumer (DTC) laboratory testing is becoming increasingly popular. DTC genetic tests allow consumers to access information about their genetics without involving their health-care professionals.
As of July 2020 the International Society of Genetic Genealogy (www.isogg.org) listed 33 DTC companies that are performing genetic testing. More than 12 million people around the world have utilized the services of one of these companies, such as 23andMe (www.23andme.com). How does this process work? Basically, you send a vial of saliva along with a fee to 23andMe. In return, you receive a report that includes (a) the proportion of your DNA that comes from each of 45 global genetic populations, (b) the origins of your maternal and paternal ancestors, and (c) a feature that allows you to connect with DNA relatives, if you opt in. The report also contains health-care information such as DNA that might not affect your health but could affect the health of your children as well as how your DNA can influence your chances of contracting certain diseases.
In 2010, GEDmatch (www.gedmatch.com) was founded to help amateur and professional genealogists and adoptees searching for birth parents. Users could upload their DNA profiles from DTC companies to identify relatives who had also uploaded their profiles. Participants could hide their names by using aliases. Each account, however, had to include an associated email address. GEDmatch provided results such as the closest matches to a user’s DNA and the estimated number of generations to a common ancestor. By December 2019 more than 1 million people had uploaded their profiles to the site.
Solving Crimes (The Good News)
The police began using GEDmatch to solve crimes, particularly cold cases. A cold case is an unsolved criminal investigation which remains active pending the discovery of new evidence. Until the authorities began using commercial genetic databases to assist with their detective work, the only DNA officially available to them were records of DNA samples provided by individuals involved with the justice system; that is, suspects and convicted criminals. This process of using commercial genetic databases led to an ongoing debate regarding public safety versus privacy concerns—in essence, the good news and the bad news about the use of our genetic data. Let’s look closely at forensic genealogy.
Forensic genealogy is the practice of analyzing genetic data from DTC companies or from companies such as GEDmatch for the purpose of identifying suspects or victims in criminal cases. Forensic genealogy uses genetic genealogy, which is the use of DNA tests in combination with traditional genealogical methods to infer biological relationships between and among individuals. Traditional genealogical methods include the study of families and family history by conducting oral interviews and researching records such as birth and marriage certificates, census data, and newspaper obituaries. The process combines data from those techniques with data from modern sources such as social media platforms, particularly Facebook.
To use forensic genealogy, law enforcement agencies have uploaded crime-scene DNA data to genetic companies to locate possible relatives of potential suspects. Genealogy experts then assemble family trees and analyze demographic identifiers. One company, Parabon NanoLabs (www.parabon-nanolabs.com), is a leader in the use of forensic genealogy as an investigative tool.
After law enforcement agencies have identified potential suspects through forensic genealogy, they use conventional investigative methods such as comparing present physical features to past eyewitness statements and police sketches. This process can often narrow down the choices to a few candidates.
Solving Cold Cases – Example 1
In 1987 a young woman left her family home for what was supposed to be a quick trip with her boyfriend. They did not return the next day, as planned. After several days, her body was found. She had been shot. Two days later, his body was found 75 miles away. He had been strangled. Police found the van they were driving in a third location.
The police collected a semen sample from the woman’s pants. By 1994, DNA analysis had advanced to the point that the semen could produce a genetic profile of the suspect. In 2003, investigators uploaded the profile to CODIS, the FBI’s criminal DNA database. The investigators hoped for a match, but CODIS did not return one.
In 2017, a detective who was examining the department’s cold cases heard that there was a way to obtain more information from DNA. He contacted Parabon. Genealogists there used the 30-year-old DNA sample and found two of the eventual suspect’s second cousins: one on his father’s side of the family, and one on his mother’s side. The firm’s lead genealogist used newspaper archives and marriage records to build a family tree dating back to the two cousins’ great-grandparents. She then found a particular couple who lived about 7 miles from where the boyfriend’s body was found.
The genealogist concentrated on that couple’s only son—who would have been 24 at the time of the murders—as a suspect. Next, an undercover officer who had begun watching the suspect picked up a cup that had fallen off his truck. The DNA from the cup and the DNA from a cheek swab provided by the suspect once in custody matched the DNA gathered 30 years before. He was convicted and sentenced to serve two life sentences concurrently. As of July 2020 he was appealing the verdict.
Solving Cold Cases – Example 2
The trail of the Golden State Killer had gone cold decades ago. The police linked him to more than 50 rapes and at least 12 murders from 1976 to 1986 but he eluded all attempts to find him. However, the police had retrieved a DNA sample from one of the crime scenes and had carefully preserved it over the years.
When the police heard about GEDmatch, they uploaded the suspect’s DNA sample. After four months of close examination, GEDmatch provided matches to relatives of the suspect, although not the suspect himself. Because the site did provide family trees, genealogists were able to find third cousins of the suspect. After examining the family tree to find a common ancestor of the third cousins, they proceeded back down the tree to identify a likely suspect. In addition to the DNA evidence, some of his victims had described him as a 5’9”, 165-pound white male, characteristics that matched his features.
In April 2018 the police arrested the suspect, who was then 72 years old and a former police officer. His DNA matched the sample taken in an earlier crime. In June 2020 he pled guilty to several counts of first-degree murder and in August the court sentenced him to 11 consecutive life sentences without the possibility of parole.
Privacy Concerns (The Bad News)
The use of open-source genetic databases has ignited a debate regarding the Fourth Amendment. This amendment states that a warrant is required in situations that violate an individual’s reasonable expectations of privacy. Given the sensitivity of information surrounding commercial genetic databases, especially regarding familial associations, courts have asserted that individuals are subject to protection under the Fourth Amendment.
Privacy advocates protested that law enforcement agencies were accessing the entire GEDmatch database without the informed consent of the users. As a result, GEDmatch began to require its customers to specifically opt in to allow law enforcement agencies to access their genetic data. The agencies objected, claiming that GEDmatch’s policy change would make it much more difficult to identify suspects and solve cold cases using genetic genealogy.
In September 2019 the U.S. Department of Justice (www.justice.gov) released interim guidelines stating that federal investigators could use forensic genealogy to discover suspects only in serious crimes such as murder and rape. The guidelines also stated that federal investigators must have a search warrant to collect DNA samples from a suspect’s relatives, who must have previously opted in to allow law enforcement agencies to access their genetic data. Because the DOJ is a federal agency, these guidelines did not apply to state or local law enforcement agencies.
In December 2019 forensic for-profit DNA analysis company Verogen (www.verogen.com) bought GEDmatch. Verogen’s CEO asserted that the site would focus on solving crimes, not just connecting family members through their DNA. He further stated that current and future users would have the ability to opt out of criminal DNA searches and that Verogen would fight “future attempts to access the data of those who have not opted in.”
Forensic genealogists then began using Family Tree DNA (www.familytreedna.com) due to the increased difficulty involved in obtaining genetic profiles from Verogen. Family Tree’s policy dictates that customers are automatically opted in unless they choose to opt out.

You must submit two separate copies (one Word file and one PDF file) using the Assignment Template on Blackboard via the allocated folder. These files must not be in compressed format.
It is your responsibility to check and make sure that you have uploaded both the correct files.
Zero mark will be given if you try to bypass the SafeAssign (e.g. misspell words, remove spaces between words, hide characters, use different character sets, convert text into image or languages other than English or any kind of manipulation).
Email submission will not be accepted.
You are advised to make your work clear and well-presented. This includes filling your information on the cover page.
You must use this template, failing which will result in zero mark.
You MUST show all your work, and text must not be converted into an image, unless specified otherwise by the question.
Late submission will result in ZERO mark.
The work should be your own, copying from students or other resources will result in ZERO mark.
Use Times New Roman font for all your answers.

You must submit two separate copies (one Word file and one PDF file) using the Assignment Template on Blackboard via the allocated folder. These files must not be in compressed format.
It is your responsibility to check and make sure that you have uploaded both the correct files.
Zero mark will be given if you try to bypass the SafeAssign (e.g. misspell words, remove spaces between words, hide characters, use different character sets, convert text into image or languages other than English or any kind of manipulation).
Email submission will not be accepted.
You are advised to make your work clear and well-presented. This includes filling your information on the cover page.
You must use this template, failing which will result in zero mark.
You MUST show all your work, and text must not be converted into an image, unless specified otherwise by the question.
Late submission will result in ZERO mark.
The work should be your own, copying from students or other resources will result in ZERO mark.
Use Times New Roman font for all your answers.

Optimize the following SQL Query by using Query Tree and Heuristic Rules:
SELECT C.Fname, E.Fname
FROM Employee AS E, Customer AS C, Order AS O
WHERE E.EMP_ID=O.E_ID AND C.City=’Jeddah’ AND C.Customer_ID=O.C_ID AND E.EMP_ID=’12943’
You should show the Initial Query Tree and add a screenshot of the Query Tree after applying each rule.

You must submit two separate copies (one Word file and one PDF file) using the Assignment Template on Blackboard via the allocated folder. These files must not be in compressed format.
It is your responsibility to check and make sure that you have uploaded both the correct files.
Zero mark will be given if you try to bypass the SafeAssign (e.g. misspell words, remove spaces between words, hide characters, use different character sets, convert text into image or languages other than English or any kind of manipulation).
Email submission will not be accepted.
You are advised to make your work clear and well-presented. This includes filling your information on the cover page.
You must use this template, failing which will result in zero mark.
You MUST show all your work, and text must not be converted into an image, unless specified otherwise by the question.
Late submission will result in ZERO mark.

Reading:
Module 6: Database Design: Relationships
Supplement Videos
Data base Design Process:

Relational Data base Relationships:

Question 1: Discussion
In this week’s reading, you learned about one-to-one and many-to-many relationships. While the concepts may seem beneficial, there are some drawbacks. For this discussion, you need to evaluate the pros and cons of each and make a recommendation regarding the usefulness of using these concepts in the JCC RDBMs.
Question 2: Assignment
You will not need to use a DBMS to complete this activity.
In an effort to further optimize the RDBMS you are developing for the JCC company, you noticed there was redundant data within the Employee Table. Following what you learned from your reading, complete the following in a 2-3 page APA-formatted paper:
Examine the Employees table. Note that the data in the Title field contains redundant values and, therefore, is a candidate for a one-field lookup table. Use your Access Query Design View or SQL skills to create a lookup table named Titles with the unique values in the Title field of the Employees table. List the steps you performed to create the lookup table.

1- A zero mark will be given if you try to bypass the SafeAssign (e.g. misspell words, remove spaces between words, hide characters, use different character sets, convert text into image or languages other than English or any kind of manipulation).
2- You are advised to make your work clear and well-presented.
3- You MUST show all your work, and text must not be converted into an image, unless specified otherwise by the question.
4- The work should be your own, copying from students or other resources will result in ZERO mark.
5- ·Use Times New Roman font for all your answers

Items to create: Tables, columns, checks, defaults.
Deliveries:
Dictionary
Database Diagram
Screen Captures of navigation panel expanded to show Bed column checks and defaults, and Age column (example provided here.
MDF and LDF files
1. Build must match dictionary, and tables must match ERD and RS design.
2. Bed table must have a checks and defaults as stated in narrative.
3. Tables must be connected with proper PKs and FKs.
7. Tables based on associate entities must have proper CPK notation for Key and Index, or use surrogate key.
8. PK of each table must be set to data type INT and Identity seed 1, increment 1.
9. Patient age must be calculated column. Hint for calculation: datediff(year,dob,getdate()).
10. Charges should be money data type, because it is already formatted for 2 decimal places.
11. Hours should be decimal (4,2) data type, so that someone can work fractions of an hour. The 4 indicates total places, the 2 indicates number of places to the right of the decimal. This is also true for the numeric data type.