Use your common sense on these. Show the SQL query and the output AND the answer

Question

Use your common sense on these. Show the SQL query and the output AND the answer to the question. DO NOT provide IDs or codes in the final answer; all tables should have meaningful column headers. Use whichever database is appropriate. Many of these require conditional logic.

1. For each US state on record in the world database, how many cities are recorded and what is their total population? Characterize them as a few (0 to 2), several (anything from 3 to 5), or lots (6 or more). You may not assume that you “know” the code for the US.

2. Characterize each African country as rural (city population < 25% of total), urban (city population > 75%) or mixed.

3. List the forms of government that are used in more than two countries, in descending order by the number of countries in which they are used.

4. Categorize contigs by the average number of ORFs they contain: a few (less than 10), many (over 50), or typical.

5. For each contig, report how many ORFs are of each length: 1-299, 300-599, 600-899, or longer.

Explanation / Answer

Keys to Successful Database Design
Planning, Planning, and Planning. Oh did I mention Planning? Seriously, planning is the largest most
significant aspect of database design and is often sold short for the raison de jour. This results in
databases which do not meet requirements, do not meet expectations, or are otherwise unwieldy.
Before switching on your computer and getting geeky using your tools of choice you should sit down
and, with pencil and paper in hand, verify your requirements, diagram your data, and plan your
database. Then proceed quickly with more paper and pointy pencils to refine a design. Then and
only then should you turn on your computer and start coding the database. But I am getting ahead
of myself.
Note: Regrettably, discussions on database design tend to suffer from a special, rather non-intuitive
terminology. I will try to offer explanations of expressions that you are likely to encounter in the text
as they come up.
Pitfalls We Wish to Avoid
Redundancy Versus Loss of Data
When designing our schema, we want to do so in such a way that we minimize redundancy of data
without losing any data. By redundancy, I mean data that is repeated in different rows of a table or in
different tables in the database.
Imagine that rather than having an employee table and a department table, we have a single table
called employeeDepartment. We can accomplish this by adding a single departmentName column to
the employee table so that the schema looks like this:
Table: employeeDepartment
employeeID name job departmentID departmentName
For each employee who works in the Department with the number 128, Research and Development,
we will repeat the data "128, Research and Development." This will be the same for each department
in the company. This schema design leads to redundantly storing the department name over and
over.
We can change this design as shown here:
Table: employee
employeeID name job departmentID
Table: department
departmentID name
In this case, each department name is stored in the database only once, rather than many times,
minimizing storage space and avoiding some problems.
Note that we must leave the departmentID in the employee table; otherwise, we lose information

2.With a population of 119,530,753 as of 2015,[1] Mexico is the most populous Spanish-speaking country in the world, the second-most populous country in Latin America after Portuguese-speaking Brazil, and the second in North America, after the United States; the third largest in the Americas after the two aforementioned countries.[4] Throughout most of the twentieth century Mexico's population was characterized by rapid growth. Although this tendency has been reversed and average annual population growth over the last five years was less than 1%, the demographic transition is still in progress, and Mexico still has a large cohort of youths. The most populous city in the country is the capital, Mexico City, with a population of 8.8 million (2010), and its metropolitan area is also the most populated with 20.1 million (2010). Approximately 50% of the population lives in one of the 55 large metropolitan areas in the country. In total, about 78.84% of the population of the country lives in urban areas, meaning that only 21.16% live in rural areas.

The Census Inegi in Mexico is the Instituto Nacional de Estadística y Geografía (INEGI). The National Population Council (CONAPO), is an institution under the Secretary of the Interior in charge of the analysis and research of population dynamics. The National Commission for the Development of Indigenous Peoples (CDI), also undertakes research and analysis of the sociodemographic and linguistic indicators of the indigenous peoples in Mexico.

3.This is a list of the countries by system of government. There is also a political mapping of the world that shows what form of government each country has, as well as a brief description of what each form of government entails. The list is color-coded according to the type of government, for example: yellow represents a Republic with an executive head of state, and pink is a constitutional monarchy with a ceremonial head of state. The color-coding also appears on the following map, representing the same government categories. The legend of what the different colors represent are found at the end of the article.

4.The transcriptome of Amur ide was sequenced using the Illumina platform and assembled into 53,632 cDNA contigs, with an average length of 647 bp and a N50 length of 1,094 bp. A total of 19,338 unique proteins were identified, and gene ontology and KEGG (Kyoto Encyclopedia of Genes and Genomes) analyses classified all contigs into functional categories. Open Reading Frames (ORFs) were detected from 34,888 (65.1%) of contigs with an average length of 577 bp, while 9,638 full-length cDNAs were identified. Comparative analyses revealed that 31,790 (59.3%) contigs have a significant similarity to zebrafish proteins, and 27,096 (50.5%), 27,524 (51.3%) and 27,996 (52.2%) to teraodon, medaka and three-spined stickleback proteins, respectively. A total of 10,395 microsatellites and 34,299 SNPs were identified and classified. A dN/dS analysis on unigenes was performed, which identified that 61 of the genes were under strong positive selection. Most of the genes are associated with stress adaptation and immunity, suggesting that the extreme alkaline-saline environment resulted in fast evolution of certain genes.

5.To make it easier for u
first genes are a series of paired nucleotides at a particular location on a chromosome, these paired nucleotides contain the basic blueprint for a particular protein.

The part of the mRNA molecule that contains this information is known as its coding region, and it is composed of a series of clusters of three nucleotides known as codons. Each of the 64 different codons designates a specific amino acid except for three: TAA, TAG and TGA, which are known as stop codons. These three codons signal the ribosome to stop reading, ending the production of the protein.

this means that a given mRNA molecule can be read in three different ways, to create three different protein sequences. To illustrate:

..T CTC AGC GTT ACC ATG A..
... Leu Ser Val Thr Met ...

The first line is the mRNA sequence ), while the second line is the corresponding amino acid sequence . This is all well and good, but what happens if you start the decoding process one nucleotide later?

.TC TCA GCG TTA CCA TGA
... Ser Ala Leu Pro STOP!

Instead of coding for the functional protein sequence Leu-Ser-Val-Thr-Met-etc., the mRNA now codes for a completely different set of amino acids, including a TGA stop codon. Thus the information in the mRNA molecule is only useful if it is read in the correct way; otherwise the end result is protein gibberish, usually coming to an abrupt halt after a few dozen codons (since 3 of the 64 codons are stop codons, almost one in twenty random codons will halt translation).

Each of the three different ways of reading mRNA are known as reading frames. Any reading frame which does not contain a stop codon is known as an open reading frame. In practice, the longest open reading frame in an mRNA is generally the one that encodes the protein. For this reason, searching for open reading frames (or ORFs, as they are commonly known) is a simple way of finding genes in a newly sequenced piece of an organism's genome. Basically, if you find a long ORF, you've probably found a gene.
hope this help.

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.